Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete blank rows (Excel 2003)

    Need to create a macro that will delete blank rows and columns. I searched the forum using several different words such as delete blank rows, just blank rows, etc. Anyway I didn't find anything so need some help. I'm sure there's a perfectly easy way to do this, but so far I haven't been able to come up with anything that fits my needs. I have to pull data from another system. Any help will be greatly appreciated.
    Data is arranged as follows:
    Columns with data are 1, 3, 5, 7
    Row 1 last name first name hours description
    Row 2 168
    Row 3 10
    Row 4 178
    row 5 blank

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete blank rows (Excel 2003)

    Are the blank rows between non blank rows?

    You might try something like this:
    <pre>Sub RemoveBlanks()
    Dim oRow as Row
    For Each oRow in Activesheet.Rows
    If Application.Counta(oRow)=0 Then
    oRow.Delete
    End If
    Next
    End Sub</pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  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 blank rows (Excel 2003)

    Try this:

    <pre>Public Sub DelBlankRowsAndColumns()
    Dim I As Long
    For I = Range("IV1").End(xlToLeft).Column - 1 To 1 Step -1
    If Application.WorksheetFunction.CountA(Columns(I)) = 0 Then
    Columns(I).Delete
    End If
    Next I
    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
    </pre>

    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks

    Thanks guys - both solutions are very useful not only for this particular spreadsheet, but for several others where I run into the same type situation.
    My life has gotten alot easier since I joined this forum!! I always get the help I need without making me feel like a moron!
    Thanks again.

Posting Permissions

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