Find the last Cell in a column in VB

  • Thread starter Thread starter dhstein
  • Start date Start date
D

dhstein

What is the code to find the last entered cell in a column? Thanks for any
help you can provide.
 
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 Rothstein said:
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.
Click to expand...
 
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---
 
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.
 
JLGWhiz said:
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.
Click to expand...

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.
 
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.
 
Or more simply:

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

regards
 
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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA set thick borders around non empty cells in range 0
Help needed 0
Last Modified By in a cell by user 3
Column number of last column that returns value ignore formulas 8
Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0
Code to do a simple clean of one column by copying and pasting(finding and replacing) phrases in adj 4
Row and column count last occupied cells in range 2
Excel Columns moving up and down on their own in a worksheet. What Goes? 2

Back
Top