Copy last row in a column in Excel with VBA

Question

I would like to copy the value of the last row in a specific Column in to another cell,

for example this is the code which I a suing to find which is the last used rown in the column G

Dim LastRow As Long

LastRow = Range("G65536").End(xlUp).Row

Right now LastRow get's the value of the address of the row, I need this code modified this way so it will copy the Value of the LastRow (not the address) and than past this value of this cell in to another Cell with address "Q1"

If anywone can Help let me know, thanks!

Answer 1

Range.Copy method has parameter which allows you to copy Range into required destination.

According to MSDN Range.Copy syntax looks as follows:

expression.Copy(Destination)
expression   A variable that represents a Range object.

In your situation you can call it as follows:

Range("G65536").End(xlUp).Copy Range("Q1")

Where Range("Q1") should be changed to any other cell if required

Answer 2

According to Microsoft MVP:

You should not use numbers because different versions of Excel have different number of maximum rows.

.Cells(.Rows.Count, "Q") is the very last cell at the bottom of column Q.

.Cells(.Rows.Count, "Q").End(xlUp).Row is the row number of the last non-blank cell in column Q.

So you should use Cells(Rows.Count, "G").End(xlUp).Row

To find number of rows used:

ActiveSheet.UsedRange.Rows.Count