Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Easley, South Carolina, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting Multiple Blank rows (Excel 97, 2000)

    Made the mistake of making a many thousand line spreadsheet with Merged Cells (3 rows deep).
    Now I want to do a conditional sum. I know that first I must remove the merging. That is simple
    enough, but now I am left with one row of data followed by two blank rows, for several thousand
    instances. Is there a quick and easy way to delete the blank lines? I know I can go in and manually
    delete the rows, but that is highly inefficient. Any advise greatly appreciated. Thanks. Thomas Cox

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

    Re: Deleting Multiple Blank rows (Excel 97, 2000)

    See if This Thread will get you started.
    Legare Coleman

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Deleting Multiple Blank rows (Excel 97, 2000)

    If your data is a simple data table without intermediate structures, sort ascending on an appropriate field will drop the blanks to the bottom rows.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Lounger
    Join Date
    Jan 2001
    Location
    Easley, South Carolina, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Multiple Blank rows (Excel 97, 2000)

    Yes, it is a start. Unfortunately, I frequently have column A blank, but have other columns with
    information that is important and should not be deleted. How would I modify the suggested
    routine to account for deleting row if Columns A-Z are all blank?

    Any information would be much appreciated.

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

    Re: Deleting Multiple Blank rows (Excel 97, 2000)

    The following should do what you want. It does rely on there being something in column A on the last row with data in it. If there is not, then you will need to put something there or in the row after the last row.

    <pre>Public Sub Delblank()
    Dim lLastRow As Long, I As Long, J As Integer
    Dim strWk As String
    lLastRow = Cells(Application.Rows.Count, Columns("A").Column).End(xlUp).Row - 1
    For I = lLastRow To 0 Step -1
    strWk = ""
    For J = 0 To 25
    strWk = strWk & ActiveSheet.Range("A1").Offset(I, J)
    Next J
    If strWk = "" Then
    Range("A1").Offset(I, 0).EntireRow.Delete
    End If
    Next I
    End Sub
    </pre>

    Legare Coleman

  6. #6
    Lounger
    Join Date
    Jan 2001
    Location
    Easley, South Carolina, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Multiple Blank rows (Excel 97, 2000)

    Thanks for the prompt reply.

    Any suggestions on books or literature to become more familiar with VBA for Excel 97, 2000?

    Regards
    Thomas Cox

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

    Re: Deleting Multiple Blank rows (Excel 97, 2000)

    I haven't bought any Excel books in about 10 years, so I'm not a good one to recommend. Maybe someone else will jump in with some suggestions.
    Legare Coleman

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Deleting Multiple Blank rows (Excel 97, 2000)

    Hi Thomas,
    For starters, any VBA books by John Walkenbach are good and if you need a slightly smaller one to carry around then I recommend Excel 2000 VBA Programmer's Reference by John Green (Wrox Press) which is full of useful information.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Deleting Multiple Blank rows (Excel 97, 2000)

    I found Learn MS Excel VBA 2000 Programming by Julitta Korol, ISBN 1-55622-703-5, to be a well structured book that will take you from beginning macro writer to intermediate VBA coder. John Walkenbach's Excel 2000 Power Programming with VBA, ISBN 0-7654-3263-4, covers most of the remaining territory.
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Multiple Blank rows (Excel 97, 2000)

    I have come across this a number of times myself.
    For a one time shot, I data fill an unused column with sequential numbers and include it in the data to be sorted.
    This way after you sort it to delete the blank rows in one fell-swoop, you can resort the data to the original order (then delete the column of sequential numbers you added). I hope this helps.
    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

Posting Permissions

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