Code to delete rows with 0 in column H

[wezdavies]

Hi,

I'm after a code to search column H pick pick up any cells with the value 0. Then to delete the entire row that has this value in them.

Any help would be appreciated.

Thanks,

Wez

 

[RoryA]

You could just use an autofilter and delete the visible rows. Would be just as quick.

 

[alvaro87]

So, one possible solution could be:

Sub Macro1()
For Each cell In Worksheets("Sheet1").Range("H:H")

If cell.Value = 0 Then
rowSelected = cell.Row
Rows(rowSelected).Select
Selection.Delete Shift:=xlUp

End If

Next
End Sub

 

[Peter_SSs]

So, one possible solution could be:

Sub Macro1()
For Each cell In Worksheets("Sheet1").Range("H:H")

If cell.Value = 0 Then
rowSelected = cell.Row
Rows(rowSelected).Select
Selection.Delete Shift:=xlUp

End If

Next
End Sub

Hmm, I wonder if you tried that yourself?
I would warn others considering trying it that it might take a long time to run as it is going to check over 1,000,000 rows and select and delete most of them quite likely.

Also, alvaro, I would suggest that you test your code on a much smaller range, say H1:20, but first put zeros in 6 or 7 consecutive cells in that range.

@wezdavies
If you decide not to go with Rory's suggestion, I would be interested know about how many rows of data you have, and the approximate proportion of those rows that might contain zeros?

 

[alvaro87]

Hi!

Yes, I know it will take a certain amount of time, but as the OP doesn't give a Range, it was safer for me to check the entire row. Anyway, the code was an aproximation that @wezdavies has to adapt to the problem he wants to solve

Thank you anyway!

 

[Peter_SSs]

Yes, I know it will take a certain amount of time, but as the OP doesn't give a Range, it was safer for me to check the entire row.

For the future, you can restrict the range to only those rows down to the last one with data like this. Then if there are only 50 rows of data, you only have to cycle through that many - a huge saving.

Dim rng As Range, cell As Range

Set rng = Range("H1", Range("H" & Rows.Count).End(xlUp))
For Each cell In rng
  'Do something
Next cell

Code like this still doesn't solve the problem of failing to delete some of the required rows if there are several consecutive ones though.