Setting range to last occupied cell

[Ken G]

I want to copy a range going from a known cell, say D4, to the last occupied
cell in column G.

I have been given the following as a suggestion to find the last occupied
row in a column, but how do I use that in a range statement in vba?
lastrow = Cells(Cells.Rows.Count, "G").End(xlUp).Row

Also will this only find the last contiguously occupied row (if that's the
right word). ie will it ignore an empty row before the row containing column
totals which I don't want to include in the range.

Is there a better solution?

 

[Leung]

lastrow = Cells(Cells.Rows.Count, "G").End(xlUp).Row

equal to:

lastrow = Cells(65536, "G").End(xlUp).Row

in vba:
= Range("G65536").End(xlUp).Row

as it start counting from G65536 till up and reach an End, just like you
press "End" then "Up Arrow" key.

for other range, just change the colume letter for the range.

 

[Jacob Skaria]

Try

Dim rngTemp As Range
Set rngTemp = Range("G1", Range("G1").End(xlDown))
MsgBox rngTemp.Address

 

[Rick Rothstein]

You can also do it this way...

Dim rngTemp As Range, LastRow As Long
LastRow = Cells(Rows.Count, "G").End(xlUp).Row
Set rngTemp = Range("D4:G" & LastRow)

or, if you didn't need the LastRow variable elsewhere in your code, you
could eliminate it by using the assigned expression directly in the Set
statement...

Dim rngTemp As Range
Set rngTemp = Range("D4:G" & Cells(Rows.Count, "G").End(xlUp).Row)