Deleting Empty Rows (Excel 2000)

Topic

New Reply

Wow–the lounge sure has changed since I was here last–great job! I know this may sound like a very lame question, but I’ve allowed my VBA skills to atrophy.

The basic challenge is simple. I need to march down a single column of cells and examine their contents. If the cell is empty, I need to delete the row; othewise I need to do stuff. Consecutive rows may be empty.

I can easily go from row to row. My problem is when I delete a row, I can’t manage to select the new, active oCell to examine the new contents (if any). Very frustrating…

I’d include my code if that would be helpful, but it sounds like it should be something one of you lizards should be able to answer in your sleep.

Thanks so much.

Jimmy May–Aspiring Geek

Reply | Quote

Replies

Does this get you started?

Sub Delete()
For Each cell In ThisWorkbook.Sheets(1).Range(“a1:a10”)
If cell = Empty Then cell.EntireRow.Delete
Next
End Sub

Reply | Quote

Michael: Your code will miss rows where there are consecutive rows with empty cells.

Reply | Quote

Right, wrote it on the fly, after testing I am wondering why it does that…any thoughts Legare?

Just looking at it, it would seem that it would work just fine…

Reply | Quote

You’re going the wrong way through the list … forward!!! You should be going backwards ( from the end upwards ), otherwise when you delete a row and go to the next, the row shifting upward will be skipped by the processing. Change your IF block to be :

If Range(“A” & I+1).Value = “” Then
Range(“A” & I+1).EntireRow.Delete
End If

Cheers, Glenn.

Reply | Quote

It does that because when you delete a row, the rows below move up. Lets say that A3 and A4 are both empty. When you delete row 3, then row 4 moves up to row 3. The next time through the loop, you look as A4 and miss the empty cell that just moved up to A3.

Reply | Quote

The following code will delete rows where Colimn A is empty:

Public Sub DelBlank()
Dim lLastRow As Long, I As Long
    lLastRow = Cells(Application.Rows.Count, Columns("A").Column).End(xlUp).Row - 1
    For I = lLastRow To 0 Step -1
        If Range("A1").Offset(I, 0) = "" Then
            Range("A1").Offset(I, 0).EntireRow.Delete
        End If
    Next I
End Sub

Reply | Quote

Thanks so much to all, and you Legare, for the final solution. Now on to my next challenge. I do so wish I had more opportunities to work with VBA–it’s so much fun (when it works)!

Reply | Quote