Name A Cell in VBA

evil_moses

New Member
Joined
Oct 27, 2008
Messages
39
Afternoon all,

Within my macro I have looped downwards to find the next empty cell. I then need to go back to this cell location (i.e. B6) later in the macro and many times. How can I go back to this cell location without performing the loop again? (which will make the code long and make the macro completion time much much longer)

The simple code below doesn't seem to do the trick - I'm sure I'm just missing something easy!!

Dim CellLocation As Range

Do Until ActiveCell = ""
ActiveCell.Offset(1, 0).Select
Loop
CellLocation = ActiveCell

Thanks for your help

Marcus
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
No need to loop. This will find the first blank cell assuming there are holes in the data

Code:
Dim LR As Long
LR = Range("A1").End(xlDown).Offset(1).Row
MsgBox LR
If you want to find the first cell with nothing below then

Code:
LR = Range("A" & Rows.Count).End(xlUp).Offset(1).Row
 

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,493
Hi,

This might be a bit more efficient (assumes that you're looking for the next empty cell in column A of the currently active worksheet):

Code:
Set CellLocation = Range(Cells(Rows.Count, "A").End(xlUp).Address).Offset(1)
 

evil_moses

New Member
Joined
Oct 27, 2008
Messages
39
VoG

If you want to find the first cell with nothing below then


Code:


LR = Range("A" & Rows.Count).End(xlUp).Offset(1).Row
</PRE>
This just gives a number relating to the row reference of the first empty cell in column A. To turn it into a range reference i added

LR = "A" & Range("A" & Rows.Count).End(xlUp).Offset(1).Row

to the beginning so that I could go to that cell over and over again by using Range(LR).Select

Just though you'd want to know - Thank you so much for your help!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,562
Office Version
365
Platform
Windows
What are you actually trying to do?

As other posters have explained there is no need to use a loop to find the last row with data or the next blank row.

And there is normally no need to use Select and ActiveCell can also cause problems.:)
 

evil_moses

New Member
Joined
Oct 27, 2008
Messages
39
In column B I am go down to the next empty cell and then moving right from there to perform other actions. Later on in the macro I need to find that cell that was empty last time and navigate to other cells from it again. I am going back to this cell many times. Do you want me to post the code as I have it to explain further?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,562
Office Version
365
Platform
Windows
Why not try something like this?
Code:
Set NextBlankCell = Range("A" & Rows.Count).End(xlUp).Offset(1)
That should create a reference to the next empty row in column A and can be used throughout any subsequent code.

For example:
Code:
NextEmptyCell.Offset(,1) = "This will put data in the next column"
 

Watch MrExcel Video

Forum statistics

Threads
1,099,626
Messages
5,469,785
Members
406,670
Latest member
Jimborusk13

This Week's Hot Topics

Top