How to create a Macro to delete rows in multiple sheets

[tctran]

Hello,

I have a spreadsheet with multiple sheets and would like to create a macro to delete the entire row(s) of each worksheet that contain a "0" value. I have a Y/N Flag column on all worksheets and want to create a macro to delete the entire row(s) on the Y/N Flag column on all sheets that has a value of "0" on the cell. Is it possible to create a macro to do this task in Excel? I've done some searches but not finding any thread similiar to this. Any help you can provide would be greatly appreciated.

Thank you,
TC

 

[venkat1926]

check whether this macro gets you what you want. if necessary modify the macro and check
check this macro in exptl data and not original data.

Option Compare Text
Sub tst()
Dim j As Long, yncol As Long, r As Range, filt As Range
For j = 1 To Worksheets.Count
With Worksheets(j)
yncol = .Rows("1:1").Cells.Find(what:="Y/N", lookat:=xlWhole).Column
Set r = .Range("A1").CurrentRegion
r.AutoFilter field:=yncol, Criteria1:="No"
r.Offset(1, 0).Resize(r.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilterMode = False
End With
Next j
End Sub

 

[tctran]

check whether this macro gets you what you want. if necessary modify the macro and check
check this macro in exptl data and not original data.

Option Compare Text
Sub tst()
Dim j As Long, yncol As Long, r As Range, filt As Range
For j = 1 To Worksheets.Count
With Worksheets(j)
yncol = .Rows("1:1").Cells.Find(what:="Y/N", lookat:=xlWhole).Column
Set r = .Range("A1").CurrentRegion
r.AutoFilter field:=yncol, Criteria1:="No"
r.Offset(1, 0).Resize(r.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilterMode = False
End With
Next j
End Sub

Thank you very much for your help! I will give it a try.