Macro required

  • Thread starter Thread starter pcorcele
  • Start date Start date
P

pcorcele

Hi
I would like a macro that will find the first empty line under col A:
I would also like that macro to activate as soon as the Excel file is opened.
Thanks
Ian M
 
pcorcele said:
I would like a macro that will find the first empty line under col A:
I would also like that macro to activate as soon as the Excel file is
opened.
Click to expand...

Depends on what you mean by "first empty line".

If you mean "the line under the bottom used row", put this in the workbook's
ThisWorkbook object:

Private Sub Workbook_Open()
Cells(Cells.SpecialCells(xlCellTypeLastCell).Row + 1, 1).Select
End Sub


If you mean "the first line that happens to have nothing in it", use this
instead:

Private Sub Workbook_Open()
For x = 1 To Cells.SpecialCells(xlCellTypeLastCell).Row
For y = 1 To Cells.SpecialCells(xlCellTypeLastCell).Column
If Len(Cells(x, y).Formula) Then GoTo notEmpty
Next
'this is what we want
Cells(x, 1).Select
Exit Sub
notEmpty:
Next
Cells(Cells.SpecialCells(xlCellTypeLastCell).Row + 1, 1).Select
End Sub


If you mean *anything else*, you'll need to clarify.
 
Try...

Cells(Rows.Count, 1).End(xlUp)(2).Select

...in Workbook_Open event or Auto_Open sub. Note that this will select
A2 if colA is empty. Otherwise, if A19 is the last non-empty cell in
colA then A20 will be selected.

If you want the first empty cell in colA below the first row...

Cells(1,1).End(xldown)(2).Select

...which assumes A1 is not empty, but there are some blank cells between
A1 and the last non-empty row in colA.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top