Deleting Multiple Blank rows (Excel 97, 2000)

Topic

New Reply

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

Reply | Quote

Replies

See if This Thread will get you started.

Reply | Quote

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.

Reply | Quote

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.

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

Reply | Quote

Thanks for the prompt reply.

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

Regards
Thomas Cox

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote