2 Columns Cells Value is Zero Then Delete EntireRow

AsifShah

Board Regular
Joined
Feb 12, 2021
Messages
70
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
Hello every one,
need a code that G column OR H column value is zero then delete entirerow. if any value is available in G or H Column then dnt delete row.
Exp:

01Dnt Delete
00delete
11Dnt Delete
i have code that work is fine if only 1 column value is zero.
VBA Code:
Sheet7.Range("A3:H3").AutoFilter field:=6, Criteria1:="="
 Sheet7.Range("A5:H150").Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
 Sheet7.AutoFilterMode = False
Thanks in Advance.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
One way:
VBA Code:
Sub MyDeleteRows()

    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column G with data
    lr = Cells(Rows.Count, "G").End(xlUp).Row
    
'   Loop through all rows backwards
    For r = lr To 3 Step -1
'       Check to see if columns G and H are both blank, and delete if they are
        If (Cells(r, "G") = 0) And (Cells(r, "H") = 0) Then Rows(r).Delete
    Next r
    
    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
Solution
One way:
VBA Code:
Sub MyDeleteRows()

    Dim lr As Long
    Dim r As Long
   
    Application.ScreenUpdating = False
   
'   Find last row in column G with data
    lr = Cells(Rows.Count, "G").End(xlUp).Row
   
'   Loop through all rows backwards
    For r = lr To 3 Step -1
'       Check to see if columns G and H are both blank, and delete if they are
        If (Cells(r, "G") = 0) And (Cells(r, "H") = 0) Then Rows(r).Delete
    Next r
   
    Application.ScreenUpdating = True
   
End Sub
Thanks Alot Jeo Coding Working Prefect.
joe when i select sheet7 then code working prefect if im on any other sheet code is not working. if it is possible otherwise leave it and Again thanks alot..
 
Upvote 0
What is the name of the module you have placed it in?
If you want it to work on any sheet, it needs to be in a General module (often named like "Module1").
If you put the code in one of the "Sheet" modules, it will only be available to that particular sheets and not the other ones.
 
Upvote 0
What is the name of the module you have placed it in?
If you want it to work on any sheet, it needs to be in a General module (often named like "Module1").
If you put the code in one of the "Sheet" modules, it will only be available to that particular sheets and not the other ones.
im using this code in userform Command Button. sheet7
 
Upvote 0
im using this code in userform Command Button. sheet7
Then it will only work on that sheet.

You can store the code in a General module and call it from your UserForm Command button.
If you store it in a General Module, it should be accessible to the other sheets.
 
Upvote 0
Then it will only work on that sheet.

You can store the code in a General module and call it from your UserForm Command button.
If you store it in a General Module, it should be accessible to the other sheets.
Thanks Dear . its fine if its work on same sheet... :)
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top