Microsoft Excel - Select first empty row - Asked By Dan on 06-Oct-11 09:57 AM

I am trying to clear data from Column A:HF down to row 3000.  Now, I must find the first empty cell in Column A:A, and make that row active.  This much I have been able to do, but how can I then select that last empty row that I made active  and also select down to row 3000, and clear all the data from A:HF?

Columns("A:A").Select
Range("A1048576").End(xlUp).Offset(1, 0).Select   ' Find first empty cell in column A

Dan
wally eye replied to Dan on 06-Oct-11 11:45 AM
You can do it in one step:

Range("A1048576").End(xlUp).offset(1,0).Resize(3000-range("A1048576").end(xlup).row,214).clearcontents

Even without the Columns.select statement it will work just fine.  The 214 is the number of columns from A to HF.
Anoop S replied to Dan on 07-Oct-11 01:44 AM
try something like this way, Using VBA

Sub Test()
Dim i, j As Integer, bEmpty As Boolean
With ActiveDocument.Tables(1)
For i = 1 To .Rows.Count
bEmpty = True
For j = 1 To .Columns.Count
If Len(.Cell(i, j).Range.Text) > 2 Then bEmpty = False
Next
If bEmpty = True Then
MsgBox "Row " & i & " is the first empty row."
Exit Sub
End If
Next
MsgBox "There is no empty row."
End With
End Sub
Dan replied to wally eye on 08-Oct-11 07:37 AM
Thank you, this work.