Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting Blank Rows (2k)

    Edited by HansV to provide link to post - see <!help=19>Help 19<!/help>.

    I did a search for a macro to delete blank rows from Excel, and found some code posted by Legare Coleman - <post#=455792>post 455792</post#>. I got rid of the blank column section since it didnt apply to me. So I tried out this sub, andi t worked well, except it didn't delete EVERY blank row I had. Turned out that some random cells down the data sheet had spaces in them. So what I want to do is delete ALL the blank rows, including ones w/ spaces. So is there an easy way to modify this code to handle that?

    Public Sub Delblankrows()
    Dim I As Long
    For I = Range("A65536").End(xlUp).Row - 1 To 1 Step -1
    If Application.WorksheetFunction.CountA(Rows(I)) = 0 Then
    Rows(I).Delete
    End If
    Next I

    End Sub
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Deleting Blank Rows (2k)

    Try this variation - it will be slower because it checks each cell:

    Public Sub Delblankrows()
    Dim I As Long
    Dim J As Long
    Dim f As Boolean
    For I = Range("A65536").End(xlUp).Row - 1 To 1 Step -1
    f = True
    For J = 1 To Cells(I, 255).End(xlToLeft).Column
    If Trim(Cells(I, J)) <> "" Then
    f = False
    End If
    Next J
    If f = True Then
    Rows(I).Delete
    End If
    Next I
    End Sub

  3. #3
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Blank Rows (2k)

    yeah! that worked perfectly. thanks a lot hans!
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

Posting Permissions

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