Move to first blank cell in a column upon opening workbook

[SanFelippo]

I have hopefully a pretty simple request; I just haven’tcoded anything like this request before so I was hoping you guys could help.
Basically, all I need is some coding that will run uponopening a an excel workbook that will take the user to the first blank cell incolumn A. That’s all.
Any suggestions? (Yes I realize you could just hold controland hit the down arrow, but the people using this workbook are very challengedwhen it comes to excel things, and this is what they want).

 

[alansidman]

Option Explicit


Private Sub Workbook_Open()
    Dim lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Range("A" & lr + 1).Select
End Sub

Place this code ThisWorkbook in the VBE

 

[SanFelippo]

This looks to be working perfectly. Thank you!

 

[DanteAmor]

Check also this way

Private Sub Workbook_Open()
    Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
End Sub

 

[Michael M]

Both work well if the col is populated, but what if the OP wants a cell somewhere within the column, not at the end ???

Private Sub Workbook_Open()
BC = Range("A1:A" & Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row
Range("A" & BC).Select
End Sub

 

[Rick Rothstein]

Both work well if the col is populated, but what if the OP wants a cell somewhere within the column, not at the end ???

Private Sub Workbook_Open()
BC = Range("A1:A" & Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row
Range("A" & BC).Select
End Sub

Go to a new, never used worksheet, fill in some values but do not leave any blanks within the data so that the first blank is after the last value... then run your code. The error results because SpecialCells cannot see beyond the worksheet's UsedRange.

 

[Michael M]

I know that Rick, that's why I said....

a cell somewhere within the column, not at the end ???

 

[Rick Rothstein]

Not the fastest code going, but the delay is hardly noticeable on my somewhat slow, older laptop and it always selects the proper blank cell...

Private Sub Workbook_Open()
  Cells([MIN(IF(A:A="",ROW(A:A)))], "A").Select
End Sub

 

[Rick Rothstein]

I know that Rick, that's why I said....

All I was pointing out is the weak point in your code similar to how you pointed out the weak point in the other posted code here. With that said, the code I posted in Message #8 works no matter where the blank cell is located at.

 

[Michael M]

Ahh....your'e a hard marker Rick...