MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Selecting a cell that is at the end of a range?


Posted by Marianne on January 16, 2001 11:56 PM

I have a macro where I want to copy information on the first line after the end of a list. To get to the end of the list I hit the End Down Arrow keys and then the down arrow once more to get me into the cell I want to be in. When I do this while I record a macro I get the following code:

Range("A9").Select
Selction.End(xlDown).Select
Range("A767").Select

The problem is that I will not always want to copy to cell "A767". It all depends on how long my list is. The way this code reads, it will always place whatever I copy to cell "A767".

What is the code that will take me down to the end of my list and then down one more cell?

Thanks so much,

Marianne

PS. I really love this website and I appreciate all the help I have received. You all are great!


Posted by JAF on January 17, 2001 1:39 AM

Hiya

The code you need is...

Sub Next_Blank_Line()
Range("A65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
End Sub

Hope this helps.


JAF

Posted by Dave Hawley on January 17, 2001 2:22 AM

Hi Marianne

JAFs code is correct, but you could shorten it to:

[A65536].End(xlUp).Cells(1, 1).Select


Dave

  • OzGrid Business Applications

Posted by JDH on January 17, 2001 4:01 AM

Should be [A65536].End(xlUp).Cells(2, 1).Select