Macro to delete rows if...

S

Sasikiran

Hello,

I need help with a macro that will delete certain rows in a spreadsheet.
The rows that need to be deleted are never in the same place, or the same
number of rows.

The row that need to be deleted will have a value 0 in two columns G & H.
Only if the value in the columns G & H is zero (0), the entire row should be
deleted. If either of the columns has value other than 0 it should remain in
the sheet.

Please suggest me on this.
 
F

Francis

Wouldn't Auto Filter does the job by deleting the rows after fitering for 0
on both columns?
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another
 
J

Jacob Skaria

Hi Sasikiran

The below will only delete if 0's are present in G and H. Note the macro
will not delete blank rows....Try and feedback

Sub DeleteRows()
For lngRow = Cells(Rows.Count, "G").End(xlUp).Row To 1 Step -1
If Range("G" & lngRow).Text = "0" And Range("H" & lngRow).Text = "0" Then
Rows(lngRow).Delete
End If
Next
End Sub

If this post helps click Yes
 
F

Francis

Assuming that your data start from row 2 with header in row 1,
this code will delete rows if only both column G and H have a value of 0
if either G or H have a value other than 0, it will remain in the sheet.
try this in a module

Sub RemoveZero()
Dim r As Long
For r = Cells(Rows.Count, 7).End(xlUp).Row To 2 Step -1
If Cells(r, 7).Value = 0 And _
Cells(r, 8).Value = 0 Then Rows(r).EntireRow.Delete
Next r
End Sub

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another
 
S

Sasikiran

Thank you so much... :)

Jacob Skaria said:
Hi Sasikiran

The below will only delete if 0's are present in G and H. Note the macro
will not delete blank rows....Try and feedback

Sub DeleteRows()
For lngRow = Cells(Rows.Count, "G").End(xlUp).Row To 1 Step -1
If Range("G" & lngRow).Text = "0" And Range("H" & lngRow).Text = "0" Then
Rows(lngRow).Delete
End If
Next
End Sub

If this post helps click Yes
 
F

Francis

Hi Sasikiran
Thanks for the feedback, glad I am of assistance.
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top