Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting Empty Rows (Excel 2000)

    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

  2. #2
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Empty Rows (Excel 2000)

    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

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Empty Rows (Excel 2000)

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

    <pre>
    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
    </pre>

    Legare Coleman

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Empty Rows (Excel 2000)

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

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Empty Rows (Excel 2000)

    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...

  6. #6
    Lounger
    Join Date
    Oct 2001
    Location
    Isleworth, Gtr London, England
    Posts
    28
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Deleting Empty Rows (Excel 2000)

    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.

  7. #7
    New Lounger
    Join Date
    Apr 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks to all!

    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)!

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Empty Rows (Excel 2000)

    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.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •