• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Explanation of below Macro

naveeddil

New Member
Hello All,

I recently requested some macro, and thanks to @p45cal who shared below macro at POST.

Code:
Sub addtoSummarysheet(Destn)    'added this sub
With Sheet1
  Set rngtocopy = Intersect(.Range("A:A"), .Rows("10:25000")).SpecialCells(xlCellTypeVisible)
  Destn.Resize(rngtocopy.Cells.Count) = .Range("P3").Value
End With
For Each are In rngtocopy.Areas
  Destn.Offset(, 1).Resize(are.Rows.Count, 2).Value = are.Resize(, 2).Value
  Destn.Offset(, 3).Resize(are.Rows.Count, 6).Value = are.Offset(, 9).Resize(, 6).Value
  Set Destn = Destn.Offset(are.Rows.Count)
Next are
End Sub

Can someone explain the below macro as I want some modification in it but am unable to understand it.
 
Last edited:
What is your level of knowledge of VBA?

Sub addtoSummarysheet(Destn) 'added this sub

This is very poor style because Destn is not given a data type. It appears to be intended to be a range.

Set rngtocopy = Intersect(.Range("A:A"), .Rows("10:25000")).SpecialCells(xlCellTypeVisible)

This line takes the intersection of column A and rows 10:25000 (which I find pointless since the ranges are static expressions) and then extracts all visible cells from that range. Then the resulting range of visible cells is assigned to rngtocopy.

This works but should be rewritten as
Set rngtocopy = Range("A10:A25000").SpecialCells(xlCellTypeVisible)


Destn.Resize(rngtocopy.Cells.Count) = .Range("P3").Value

This returns a range that starts as Destn but then is resized to have the same number of rows as the range rngtocopy created above. Then it fills all of those cell with the value of cell P3.


For Each are In rngtocopy.Areas

Variable are is undeclared, again poor style. An Area is a contiguous block in a range. Because rngtocopy has only visible cells, it may have multiple discontiguous Areas.

Destn.Offset(, 1).Resize(are.Rows.Count, 2).Value = are.Resize(, 2).Value
Destn.Offset(, 3).Resize(are.Rows.Count, 6).Value = are.Offset(, 9).Resize(, 6).Value


This copies the values from first two columns of the current area are to columns 2 and 3 of Destn, using however many rows are in are.

Set Destn = Destn.Offset(are.Rows.Count)

This relocated Destn down after the rows that were just given values, to prepare for the next set of values.

So in short, this code finds all visible cells in a range, and copies them into a single continuous block in a destination range.
 

naveeddil

New Member
I am very much new to the VBA and can only modify whenever required in existing codes!

Thanks for the clarification. I'll get back for some support if failed
 
Top