Delete rows with cell value 0 in column M | Excel forum

Resolved · High Priority · Version 365

Tue 20 Jun 2023, 08:37 Delegate Lauren said...

Lauren has attended:
Excel VBA Introduction course

Delete rows with cell value 0 in column M

Hi,

I wrote the below macro to get all the rows with 0 value in column M deleted. It deletes every other row with 0 value because once one row has been deleted, the rows move up (included one that may have a zero value in column M) and when it goes to the next cell below it misses deleting the row that moved up. To solve this I added the text: ActiveCell.Offset(-1,0).Select so that it would go up one cell after deleting a row. This creates another problem: it deletes the first row with a zero value and then stops. I think this is because when it goes up one row after deleting the first one it finds the heading with does not equal to zero and stops. Please could you assist.

Thanks,
Lauren

For Each Cell In Range("M8:M500")
If Cell.Value = 0 Then
Cell.EntireRow.Delete
'ActiveCell.Offset(-1, 0).Select
End If
Next Cell

Tue 20 Jun 2023, 16:47 Trainer Jens said...

RE: delete rows with cell value 0 in column M

Hi Lauren,

Thank you for the forum question.

Yes the FOR EACH LOOP starts from the top and will skip rows when rows are deleted. You will need to do it from bottom and up. You can use a FOR NEXT LOOP.

Please try below suggestion.

Sub DeleteRow()
Dim r As Long

For r = 500 To 8 Step -1
If Cells(r, 13) = 0 Then
Rows(r).Delete
End If
Next r
End Sub

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector