Solved: Last Used Cell

[drums4monty]

Hi All

How do I check for the last cell that has data in, e.g. I have a sheet which has serial numbers in col A, and in Col B there are Names, some cells are empty, I need to get to the very last cell that has data in Col B, is this possible?

Alan

[RichardSchollar]

Hello Alan

Perhaps:

Dim myCell As Range
 
Set myCell = Cells(Application.Rows.Count,"B").End(xlUp)

which will give you a range reference to the last cell in column B containing a value on the Activesheet.

Richard

[royUK]

You can also use Specialcells

Dim rlastCl As Range
'change the sheet to suit
Set rlastCl = Sheet1.Cells.SpecialCells(xlCellTypeLastCell)
MsgBox rlastCl.Address

[drums4monty]

I have tried both options. The first option does notihng and the second option gives me a box with $A$1 in it. I changed Sheet1 to the sheet name which is Day but then get an error. I added both options as a Sub(), is that right?

Alan

[rory]

Alan,
The first piece of code gives you a reference to the cell - it doesn't do anything with it: that's up to you. If you wanted to select it, add this line:
[VBA]myCell.Select[/VBA]
to the end of the sub.
The second option uses the worksheet's code name, which is not necessarily the same as what you see on the tab - try:
[VBA]

Dim rlastCl As Range
'change the sheet to suit
Set rlastCl = Worksheets("Day").Cells.SpecialCells(xlCellTypeLastCell)
MsgBox rlastCl.Address

[/VBA]
Regards,
Rory

[drums4monty]

Sorry Rory, still no luck, I have a lot to learn.

ALan

[rory]

What do you mean by "still no luck"? Are you getting errors, or nothing happening or what?

[drums4monty]

I've got the first one working now Rory, I was obviously doing something wrong.

Many thanks both of you.

Alan

[rory]

Glad you got it sorted!
Rory