Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Delete empty rows (Excel xp)

    I have this procedure to delete empty rows. It delete the row, however, if something is blank in COL A but has data in COL B through AZ. I need it to look in all the columns of the row to make sure they are blank before deleting the entire row. How can i modify this procedure? thank you


    Private Sub DeleteRowsEmpty()

    With ActiveSheet
    lLastRow = .Range("A65536").End(xlUp).Row
    End With

    With ActiveSheet.Range("A1")
    For i = lLastRow To 0 Step -1
    If Trim(.Offset(i, 0) & .Offset(i, 1)) = "" Then
    .Offset(i, 0).EntireRow.Delete
    End If
    Next i
    End With

    End Sub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Delete empty rows (Excel xp)

    Instead of a macro, I prefer the autofilter.
    Add a column and in the cells enter (assume the col is BA and you start in row 2, 1 is header)
    BA2 is:
    =counta(A2:Az2)=0
    Copy this through the rows and you will get either TRUE (all are blank) or False (contains at least 1 non-blank)

    Autofilter on "TRUE" for BA and then delete those rows

    Steve

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

    Re: Delete empty rows (Excel xp)

    I would do it like this:

    <pre>Private Sub DeleteRowsEmpty()
    Dim lLastRow As Long, I As Long, J As Long
    With ActiveSheet
    lLastRow = .UsedRange.Row + .UsedRange.Rows.Count - 2
    End With
    With ActiveSheet.Range("A1")
    For I = lLastRow To 0 Step -1
    strWk = ""
    For J = 0 To 255
    If Trim(.Offset(I, J)) <> "" Then Exit For
    Next J
    If J > 255 Then
    .Offset(I, 0).EntireRow.Delete
    End If
    Next I
    End With
    End Sub
    </pre>

    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
  •