• Deleting Empty Rows (Excel 2000)

    Author
    Topic
    #369237

    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

    Viewing 1 reply thread
    Author
    Replies
    • #580901

      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

      • #580906

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

        • #580908

          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…

          • #580927

            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.

          • #580959

            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.

    • #580905

      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
      
      • #580935

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

    Viewing 1 reply thread
    Reply To: Deleting Empty Rows (Excel 2000)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: