PDA

View Full Version : Solved: Last Used Cell



drums4monty
07-30-2007, 04:03 AM
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
07-30-2007, 04:11 AM
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
07-30-2007, 05:07 AM
You can also use Specialcells


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

drums4monty
07-30-2007, 06:09 AM
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
07-30-2007, 06:30 AM
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:
myCell.Select
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:


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

Regards,
Rory

drums4monty
07-30-2007, 07:10 AM
Sorry Rory, still no luck, I have a lot to learn.

ALan

rory
07-30-2007, 07:21 AM
What do you mean by "still no luck"? Are you getting errors, or nothing happening or what?

drums4monty
07-30-2007, 08:08 AM
I've got the first one working now Rory, I was obviously doing something wrong.

Many thanks both of you.

Alan

rory
07-30-2007, 08:17 AM
Glad you got it sorted! :)
Rory