Next clear cell in a table

[jamienwood]

I know it can be done and its probably really easy but does anyone know how to get to the next clear cell in a column, and select it. For example

Sub NextClearCell()
Range("A1").Select
Selection.End(xlDown).Select
End

This will take me to the last cell with something in but how do i get to the next empty cell???

[Tom Schreiner]

Hi
Maybe this?

Sub NextClearCell()
Range("A1").Select
Selection.End(xlDown).Activate
Range("A" & ActiveCell.Row + 1).Select
End Sub

Tom

[ This Message was edited by: TsTom on 2002-04-25 05:03 ]

[jamienwood]

it just selects A1?? rather than the botom of the table

[Audiojoe]

Try this:

Application.Goto Reference:="R65536C1"
Selection.End(xlUp).Select

[Tom Schreiner]

Sub NextClearCell()
Dim LastRow
NextRow = Range("a1:a" & Range("A65536").End(xlUp).Row).Rows.Count + 1
Cells(NextRow, 1).Select
End Sub

Joe,
I tried yours and it selects the last used cell of the range.
This one does work.

[jamienwood]

cheers for your help so far but when i tried to change this code so it works on column E instead of A it will find the next clear row but selects the next clear row in column A even if the data is in E
This is what i changed from

Sub NextClearCell()
Dim LastRow
NextRow = Range("a1:a" & Range("A65536").End(xlUp).Row).Rows.Count + 1
Cells(NextRow, 1).Select
End Sub

to

Sub NextClearCell()
Dim LastRow
NextRow = Range("E1:E" & Range("E65536").End(xlUp).Row).Rows.Count + 1
Cells(NextRow, 1).Select
End Sub

[jamienwood]

Can anyone help????

[Tom Schreiner]

Sub NextClearCell()
Dim LastRow
NextRow = Range("E1:E" & Range("E65536").End(xlUp).Row).Rows.Count + 1
Cells(NextRow, 5).Select
End Sub



Culprit = Cells(NextRow, 1).Select
where 1 = column 1
change to 5

Tom

[jamienwood]

Thanks TsTom that works wonders it also works if you replace the column reference number (After [NextRow, #]) with the letter in "" e.g. "E". Just incase anyone ever needs this, because it is really useful!!

Sub NextClearCell()
Dim LastRow
NextRow = Range("E1:E" & Range("E65536").End(xlUp).Row).Rows.Count + 1
Cells(NextRow, "E").Select
End Sub