Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Delete rows with different criteria
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,069
Default 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"
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
ben ben is offline
external usenet poster
  
Posts: 232
Default 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"
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,069
Default 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"
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 692
Default 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"


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,069
Default 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"
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
ben ben is offline
external usenet poster
  
Posts: 232
Default 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"



Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 59
Default 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"


Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,069
Default 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"



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Delete all rows if criteria not matched ongcong Excel Discussion (Misc queries) 4 August 25th 08 07:58 PM
Delete rows based on certain criteria Coal Miner Excel Discussion (Misc queries) 2 March 3rd 06 05:56 PM
Delete rows where cells contain various criteria gmunro Excel Programming 1 March 10th 05 02:27 PM
delete rows with criteria S.E. Excel Programming 5 September 9th 04 04:04 PM
Delete rows w/o criteria RickK[_2_] Excel Programming 2 October 31st 03 04:48 PM


All times are GMT +1. The time now is 07:57 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement