Reference Last Cell In Column

clord · Oct 18th 2006

I am recording a macro which will update formulas in a worksheet based on a new table which is pasted into a different worksheet monthly. I am trying to reference the last cell containing text in a column. I have tried using Ctrl downarrow while recording the macro, but when I look at the code, a cell reference is used. To test out the macro I inserted more rows in the main sheet and it is not referencing the last cell, regardless of how many rows there are. How can I get excel to "go to the last entry in this column" and use that cell as a reference on another sheet. Each month I want to run the macro so it will automatically use values from the last row, whatever the row number.

Barrie Davidson · Oct 18th 2006

Re: Reference Last Cell In Column

Code

Range("H65536").End (xlUp)

will give you the last cell in column H, change the reference accordingly.

Hope this helps you out.

venkat1926 · Oct 18th 2006

Re: Reference Last Cell In Column

try somethng like this

Code

Sub test()
Dim x As Range
Worksheets("sheet1").Activate
Set x = Cells(Rows.Count, "a").End(xlUp)
MsgBox x.Address
Worksheets("sheet2").Activate
Cells(x.Row, "a") = "venkat"
End Sub

this is only a trivial example. you have to modify to suit you.

Badpower · Feb 28th 2013

Re: Reference Last Cell In Column

Quote from Barrie Davidson;303913
Code
Range("H65536").End (xlUp)
will give you the last cell in column H, change the reference accordingly.

Hope this helps you out.

For Excel 2010, last cell is not 65536 so to make sure your code runs well on any Excel version I'd go with:

Code

Sheets(1).Cells(Sheets(1).Cells.Rows.Count, "B").End(xlUp).Row

[INDENT]Where 1 specifies Sheet index (and can be replaced by Sheet name enclosed in double quotes) and "B" specifies the Column you intend to find last used cell.[/INDENT]

tears

Reference Last Cell In Column

Participate now!

Share