I will try to explain. First, anything inside the With/End With that is
preceded by a period refers back to the object in the first line of the With
statement - "Sheet2" in this case.

With Worksheets("Sheet2")
Set rngTest = .Range("B2", _
.Cells(.Range("D2").End(xlDown).Row, 2))
End With

Range/Cell references inside of other range references also must refer to
the proper worksheet, or Excel will think you mean the active sheet (like the
reference to .Range("D2") above

One way a range can be defined by the beginning and ending cell. You know
the beginning cell is B2. We find the second cell by defining a row and
column reference.

..Cells(row,column)

the row is found by starting in D2 and jumping to the last cell that is
contiguous (similar to you selecting cell D2, holding down the Control key
and hitting the down arrow). This statement refers to that cell and we add
..Row on the end to get just the row reference of that cell.

..Range("D2").End(xlDown).Row

The 2 in the Cells reference refers to column B, as you suspected. So, you
could use

With Worksheets("Sheet2")
Set rngTest = .Range("A2", _
.Cells(.Range("D2").End(xlDown).Row, 1))
End With

You could also save the Row reference of the last cell in column D to a
variable if you have to use it many times.

LastRow = WorkSheets("Sheet2").Range("D2").End(xlDown).Row



"shelfish" wrote:

I'm trying to copy this for use in column A as well but with no luck. I
think it must be that "2" at the end. What does it represent?