Delete Rows with Zeros (97sr2)

Topic

New Reply

I have a column of data – 50K rows. Most of the data is zeros. But, there are about 9000 cells that have data in them. I want to know the fastest, easiest way of deleting all the rows with zeros so that all I have left are the rows with data.

Example:
0
0
0
15
0
0
0
0
0
6

Delete all zeros and have a column that has values greater than zero.

How can I do this the easiest? I don’t know code.

Reply | Quote

Replies

DUH! Sometimes I make things much more difficult than they need to be. So sorry for the waste of a post. I sorted the data …. ahhh.

Reply | Quote

Of course, if you want to retain the original data order, sorting might not work …

The following macro goes through column A, deleting any rows that have zero values, are blank or have text (eg tick marks or dashes):

Sub CleanUp()

Dim A As Long
Dim C As Range

On Error GoTo Abort
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set C = ActiveSheet.UsedRange.Rows
For A = C.Rows.Count To 1 Step -1
If Application.WorksheetFunction.Sum(C.Rows(A).Columns(“A:A”)) = 0 Then
C.Rows(A).EntireRow.Delete
End If
Next A

Abort:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

PS: Once you’ve added it to the worksheet, it’ll beat sorting and deleting the unwanted rows any time.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Another option is to use advanced filter with the criteria set to <0, then click the radio button to have filtered data copied to another part of the worksheet.
This way you retain your original data and have the filtered data available to work with. HTH

Reply | Quote

Thanks to both of you. What I ended up doing was adding an ID Number column in which I numbered the rows with a fill. Then I sorted on Column A (with the zeros) in descending order. Copied, pasted both colums of data (not including the zeros) to another part of the worksheet. Then I sorted by the id column (data was back in the original order). Not very elegant, but it was quick and worked great. I will, however, print out your suggestions for future use.
Thanks!

Reply | Quote