Delete rows (Excel 2000)

Topic

New Reply

I have reviewed the threads on ‘Delete Rows’ and they don’t solve my problem. I have a list of Data in Columns A – Q that varies in the number of rows each month. I need a statement that will delete the row if Cell A is blank. If Cell A is occupied, keep the row. This needs to go down to A65536. Thank you.

Reply | Quote

Replies

You can use a macro like this:

Sub DeleteRows()
Dim lngRow As Long
Dim lngMaxRow As Long

lngMaxRow = Range(“A65536”).End(xlUp).Row
For lngRow = lngMaxRow To 1 Step -1
If Range(“A” & lngRow) = “” Then
Range(“A” & lngRow).EntireRow.Delete
End If
Next lngRow
End Sub

Reply | Quote

If column A can have empty cells, then your code will miss deleting any rows at the end if column A is empty. I think you need to find out if any of the columns are guranteed to always have a value and use that column. Cindy, are any of those columns guranteed to always have a value?

Reply | Quote

How about something simple like:

Sub DeleteBlankA()
    Range("A1:A65536").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

This should be much faster than looping…

Steve

Reply | Quote

Very Nice!!!!

Reply | Quote

Excellent!

Reply | Quote

Awesome!!!!!!!!!!! Thank you.

Reply | Quote