|
Delete rows with different criteria
I have a simple macro that filters (hides) the data that is unapplicable in
each worksheet. How would I make the macro delete the rows that it is hiding in the autofilter? My code is below. THANKS! Selection.AutoFilter Field:=8, Criteria1:="<", Operator:=xlAnd Selection.AutoFilter Field:=14, Criteria1:="0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="=7.00", Operator:=xlAnd Sheets("AG Non Call").Select Selection.AutoFilter Field:=8, Criteria1:="=#N/A N Ap", Operator:=xlAnd Selection.AutoFilter Field:=14, Criteria1:="0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="=7.00", Operator:=xlAnd Sheets("CORPS").Select Selection.AutoFilter Field:=14, Criteria1:="0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="=8.00" |
Delete rows with different criteria
change your criteria to show ONLY THE LINES YOU PREVIOUSLY HAD HIDDEN and
then delete all rows Selection.AutoFilter Field:=8, Criteria1:="<", Operator:=xlAnd Selection.AutoFilter Field:=14, Criteria1:="=0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="<7.00", Operator:=xlAnd activesheet.usedrange.delete Sheets("AG Non Call").Select Selection.AutoFilter Field:=8, Criteria1:="<#N/A N Ap", Operator:=xlAnd Selection.AutoFilter Field:=14, Criteria1:="<0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="<7.00", Operator:=xlAnd Sheets("CORPS").Select Selection.AutoFilter Field:=14, Criteria1:="<0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="<8.00" activesheet.usedrange.delete test this code before finalizing and saving -- When you lose your mind, you free your life. "John" wrote: I have a simple macro that filters (hides) the data that is unapplicable in each worksheet. How would I make the macro delete the rows that it is hiding in the autofilter? My code is below. THANKS! Selection.AutoFilter Field:=8, Criteria1:="<", Operator:=xlAnd Selection.AutoFilter Field:=14, Criteria1:="0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="=7.00", Operator:=xlAnd Sheets("AG Non Call").Select Selection.AutoFilter Field:=8, Criteria1:="=#N/A N Ap", Operator:=xlAnd Selection.AutoFilter Field:=14, Criteria1:="0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="=7.00", Operator:=xlAnd Sheets("CORPS").Select Selection.AutoFilter Field:=14, Criteria1:="0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="=8.00" |
Delete rows with different criteria
ben, I will try that, but you didn't change the first line... should that
change? Selection.AutoFilter Field:=8, Criteria1:="<", Operator:=xlAnd "ben" wrote: change your criteria to show ONLY THE LINES YOU PREVIOUSLY HAD HIDDEN and then delete all rows Selection.AutoFilter Field:=8, Criteria1:="<", Operator:=xlAnd Selection.AutoFilter Field:=14, Criteria1:="=0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="<7.00", Operator:=xlAnd activesheet.usedrange.delete Sheets("AG Non Call").Select Selection.AutoFilter Field:=8, Criteria1:="<#N/A N Ap", Operator:=xlAnd Selection.AutoFilter Field:=14, Criteria1:="<0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="<7.00", Operator:=xlAnd Sheets("CORPS").Select Selection.AutoFilter Field:=14, Criteria1:="<0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="<8.00" activesheet.usedrange.delete test this code before finalizing and saving -- When you lose your mind, you free your life. "John" wrote: I have a simple macro that filters (hides) the data that is unapplicable in each worksheet. How would I make the macro delete the rows that it is hiding in the autofilter? My code is below. THANKS! Selection.AutoFilter Field:=8, Criteria1:="<", Operator:=xlAnd Selection.AutoFilter Field:=14, Criteria1:="0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="=7.00", Operator:=xlAnd Sheets("AG Non Call").Select Selection.AutoFilter Field:=8, Criteria1:="=#N/A N Ap", Operator:=xlAnd Selection.AutoFilter Field:=14, Criteria1:="0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="=7.00", Operator:=xlAnd Sheets("CORPS").Select Selection.AutoFilter Field:=14, Criteria1:="0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="=8.00" |
Delete rows with different criteria
You can use something like this to select visible cells
and than copy them to another location Selection.SpecialCells(xlCellTypeVisible).Select or something like this Dim rw As Long, lrw As Long lrw = Cells(Rows.Count, "A").End(xlUp).Row For rw = lrw To 1 Step -1 If Rows(re).Hidden = True Then MsgBox Rows(x).Address End If Next -- steveB Remove "AYN" from email to respond "John" wrote in message ... I have a simple macro that filters (hides) the data that is unapplicable in each worksheet. How would I make the macro delete the rows that it is hiding in the autofilter? My code is below. THANKS! Selection.AutoFilter Field:=8, Criteria1:="<", Operator:=xlAnd Selection.AutoFilter Field:=14, Criteria1:="0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="=7.00", Operator:=xlAnd Sheets("AG Non Call").Select Selection.AutoFilter Field:=8, Criteria1:="=#N/A N Ap", Operator:=xlAnd Selection.AutoFilter Field:=14, Criteria1:="0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="=7.00", Operator:=xlAnd Sheets("CORPS").Select Selection.AutoFilter Field:=14, Criteria1:="0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="=8.00" |
Delete rows with different criteria
That didn't quite work. I don't think I can delete the "Used range" because
I have stuff in rows 1 through 10 that I want to keep. Does that make sense? I think it may also be hiding all the fields and not filtering them correctly. I use 700 rows and on two of the sheets 700 rows are hidden... none are deleted. "ben" wrote: change your criteria to show ONLY THE LINES YOU PREVIOUSLY HAD HIDDEN and then delete all rows Selection.AutoFilter Field:=8, Criteria1:="<", Operator:=xlAnd Selection.AutoFilter Field:=14, Criteria1:="=0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="<7.00", Operator:=xlAnd activesheet.usedrange.delete Sheets("AG Non Call").Select Selection.AutoFilter Field:=8, Criteria1:="<#N/A N Ap", Operator:=xlAnd Selection.AutoFilter Field:=14, Criteria1:="<0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="<7.00", Operator:=xlAnd Sheets("CORPS").Select Selection.AutoFilter Field:=14, Criteria1:="<0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="<8.00" activesheet.usedrange.delete test this code before finalizing and saving -- When you lose your mind, you free your life. "John" wrote: I have a simple macro that filters (hides) the data that is unapplicable in each worksheet. How would I make the macro delete the rows that it is hiding in the autofilter? My code is below. THANKS! Selection.AutoFilter Field:=8, Criteria1:="<", Operator:=xlAnd Selection.AutoFilter Field:=14, Criteria1:="0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="=7.00", Operator:=xlAnd Sheets("AG Non Call").Select Selection.AutoFilter Field:=8, Criteria1:="=#N/A N Ap", Operator:=xlAnd Selection.AutoFilter Field:=14, Criteria1:="0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="=7.00", Operator:=xlAnd Sheets("CORPS").Select Selection.AutoFilter Field:=14, Criteria1:="0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="=8.00" |
Delete rows with different criteria
play with your criteria a little until you have only the rows showing you
want to delete then activesheet.rows("11:2000").delete -- When you lose your mind, you free your life. "STEVE BELL" wrote: You can use something like this to select visible cells and than copy them to another location Selection.SpecialCells(xlCellTypeVisible).Select or something like this Dim rw As Long, lrw As Long lrw = Cells(Rows.Count, "A").End(xlUp).Row For rw = lrw To 1 Step -1 If Rows(re).Hidden = True Then MsgBox Rows(x).Address End If Next -- steveB Remove "AYN" from email to respond "John" wrote in message ... I have a simple macro that filters (hides) the data that is unapplicable in each worksheet. How would I make the macro delete the rows that it is hiding in the autofilter? My code is below. THANKS! Selection.AutoFilter Field:=8, Criteria1:="<", Operator:=xlAnd Selection.AutoFilter Field:=14, Criteria1:="0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="=7.00", Operator:=xlAnd Sheets("AG Non Call").Select Selection.AutoFilter Field:=8, Criteria1:="=#N/A N Ap", Operator:=xlAnd Selection.AutoFilter Field:=14, Criteria1:="0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="=7.00", Operator:=xlAnd Sheets("CORPS").Select Selection.AutoFilter Field:=14, Criteria1:="0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="=8.00" |
Delete rows with different criteria
Since there is not a SpecialCells(xlCellTypeHidden), reverse your criteria
to hide the data to be kept within your range then delete the remaining visible rows. (untested) Selection.AutoFilter Field:=8, Criteria1:="<", Operator:=xlAnd Selection.AutoFilter Field:=14, Criteria1:="0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="=7.00", Operator:=xlAnd Sheets("AG Non Call").Select Selection.AutoFilter Field:=8, Criteria1:="=#N/A N Ap", Operator:=xlAnd Selection.AutoFilter Field:=14, Criteria1:="0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="=7.00", Operator:=xlAnd Sheets("CORPS").Select Selection.AutoFilter Field:=14, Criteria1:="0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="=8.00" Selection.SpecialCells(xlCellTypeVisible).EntireRo w.Delete "John" wrote in message ... I have a simple macro that filters (hides) the data that is unapplicable in each worksheet. How would I make the macro delete the rows that it is hiding in the autofilter? My code is below. THANKS! Selection.AutoFilter Field:=8, Criteria1:="<", Operator:=xlAnd Selection.AutoFilter Field:=14, Criteria1:="0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="=7.00", Operator:=xlAnd Sheets("AG Non Call").Select Selection.AutoFilter Field:=8, Criteria1:="=#N/A N Ap", Operator:=xlAnd Selection.AutoFilter Field:=14, Criteria1:="0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="=7.00", Operator:=xlAnd Sheets("CORPS").Select Selection.AutoFilter Field:=14, Criteria1:="0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="=8.00" |
Delete rows with different criteria
Well, as I am working on getting the right criteria...
Ben your second delete function works, the other ones don't... they give me an error(400). thanks for the help! "Jef Gorbach" wrote: Since there is not a SpecialCells(xlCellTypeHidden), reverse your criteria to hide the data to be kept within your range then delete the remaining visible rows. (untested) Selection.AutoFilter Field:=8, Criteria1:="<", Operator:=xlAnd Selection.AutoFilter Field:=14, Criteria1:="0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="=7.00", Operator:=xlAnd Sheets("AG Non Call").Select Selection.AutoFilter Field:=8, Criteria1:="=#N/A N Ap", Operator:=xlAnd Selection.AutoFilter Field:=14, Criteria1:="0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="=7.00", Operator:=xlAnd Sheets("CORPS").Select Selection.AutoFilter Field:=14, Criteria1:="0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="=8.00" Selection.SpecialCells(xlCellTypeVisible).EntireRo w.Delete "John" wrote in message ... I have a simple macro that filters (hides) the data that is unapplicable in each worksheet. How would I make the macro delete the rows that it is hiding in the autofilter? My code is below. THANKS! Selection.AutoFilter Field:=8, Criteria1:="<", Operator:=xlAnd Selection.AutoFilter Field:=14, Criteria1:="0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="=7.00", Operator:=xlAnd Sheets("AG Non Call").Select Selection.AutoFilter Field:=8, Criteria1:="=#N/A N Ap", Operator:=xlAnd Selection.AutoFilter Field:=14, Criteria1:="0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="=7.00", Operator:=xlAnd Sheets("CORPS").Select Selection.AutoFilter Field:=14, Criteria1:="0", Operator:=xlAnd Selection.AutoFilter Field:=18, Criteria1:="=8.00" |
| All times are GMT +1. The time now is 12:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com