Name A Cell in VBA

[evil_moses]

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

 

[Jon von der Heyden]

Try:

Set CellLocation = ActiveCell

 

[Makrini]

try

SET CellLocation = ActiveCell

 

[VoG]

No need to loop. This will find the first blank cell assuming there are holes in the data

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

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

 

[al_b_cnu]

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):

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

 

[evil_moses]

Tell me about it!

 

[evil_moses]

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]

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]

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]

Why not try something like this?

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:

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