Find the last Cell in a column in VB

[dhstein]

What is the code to find the last entered cell in a column? Thanks for any
help you can provide.

 

[Rick Rothstein]

This will set a reference to the last cell in Column C on the active sheet.

Dim LastCell As Range
Set LastCell = Cells(Rows.Count, "C").End(xlUp)

If you want to reference the last cell on a specific sheet...

Dim Last Cell As Range
With Worksheets("Sheet1")
Set LastCell = .Cells(.Rows.Count, "C").End(xlUp)
End With

Note the dotted Cells and Rows property calls to reference the sheet in the With statement.

 

[dhstein]

This will set a reference to the last cell in Column C on the active sheet.

Dim LastCell As Range
Set LastCell = Cells(Rows.Count, "C").End(xlUp)

If you want to reference the last cell on a specific sheet...

Dim Last Cell As Range
With Worksheets("Sheet1")
Set LastCell = .Cells(.Rows.Count, "C").End(xlUp)
End With

Note the dotted Cells and Rows property calls to reference the sheet in the With statement.

--
Rick (MVP - Excel)



Rick, Thanks so much for your response. I have to try it now. But I realize that I may need something else, because this is part of a routine that will read in a file each day into a fixed location. One day the file may go to row 1000 and the next day it may go to row 1050 or 950. So I wonder if that function will work in that case or will it show the maximum cell that was ever used? In that case I would probably need the last Nonblank cell in the row. Thanks for any information on this.

 

[ryguy7272]

I think Rick gave you the answer you were looking for. Did you try it and
see if it does what you need? As an alternative, or in addition, you may
want to consider going to the and of the used range. This tiny macro does
that for you:

Sub Macro1()
Sheets("Sheet1").Select
ActiveCell.SpecialCells(xlLastCell).Select
End Sub


Regards,
Ryan---

 

[JLGWhiz]

In Excel2003 it will max out at 65535 because the row count is 65536. In
Excell2007, there are considerably more rows and you probably would not have
any problem for quite a while.

 

[dhstein]

In Excel2003 it will max out at 65535 because the row count is 65536. In
Excell2007, there are considerably more rows and you probably would not have
any problem for quite a while.

Thanks for the responses. I tried it and it worked ... except I wasn't
clear ... I need the row number not the contents. Thanks for any help.

 

[Rick Rothstein]

Try these then. On active sheet...

Dim LastCell As Range
LastCellsRow = Cells(Rows.Count, "C").End(xlUp).Row

On a specific sheet...

Dim Last Cell As Range
With Worksheets("Sheet1")
LastCellsRow = .Cells(.Rows.Count, "C").End(xlUp).Row
End With

Again, note the dotted Cells and Rows property calls to reference the sheet in the With statement.

 

[Coder1215]

Or more simply:

LastRow = range("C:C").SpecialCells(xlCellTypeLastCell).Row

regards

 

[Gord Dibben]

Which is not reliable if used range has not been re-set.

Enter data in column C down to row 100 then run the code which will return
100

lastrow = Range("C:C").SpecialCells(xlCellTypeLastCell).Row
MsgBox lastrow

Clear Contents from C51:C100 then run again.

You still get 100


Gord Dibben MS Excel MVP