RE: A little Macro Help Please ...

Tech-Archive recommends: Fix windows errors by optimizing your registry

One more thing ... I have "multiple" checks to make & I wish to "clear
contents" each time Criteria found ... & I wish to MOVE ON each time Criteria
is NOT found.

Thanks ... Kha

"mark" wrote:

Ahh... good. Sorry... I wasn't sure what you meant; perhaps I didn't read
carefully enough the first time.. sorry.

Anyway, here, this will do what you need.

'***********
Selection.AutoFilter Field:=1, Criteria1:="<>32*", Operator:=xlAnd
Range("A3:N2000").Select

If Range("a3:N2000").SpecialCells(xlCellTypeVisible).Count <>
Range("a3:N2000").Cells.Count Then

Selection.ClearContents

End If
'************

The special cells xlcelltypevisible bit will check if the autofilter has
hidden any rows, or not, and only run the clearcontents if the two counts
(total cells vs. visible cells) don't match.



"Ken" wrote:

What I wish:

1: Criteria found ... Clear visible cells in Range A3:N2000.
2: Criteria not found ...Move on without clearing anything.

As it is:

1: Criteria found ... Clears visible cells in Range A3:N2000.
2: Criteria not found ... Clears EVERYTHING in Range A3:N2000.

Thanks ... Kha




"mark" wrote:

your code is set to clear everything left visible in the range A3:N2000 after
the application of the AutoFilter.

what do you want it to do? You didn't actually say.

If you want it to apply the filter, but not delete anything, then just take
out the two line:

Selection.ClearContents





"Ken" wrote:

Excel2003 ... I know nothing of VB Code ... I record Macros only & then
perform a little creative cut/paste as needed ... That said ... I have the
following recorded Macro instruction:

Range("A2").Select
Selection.AutoFilter Field:=1, Criteria1:="<>32*", Operator:=xlAnd
Range("A3:N2000").Select
Selection.ClearContents

Issue is ... ALL Data is cleared from the Range ("A3:N2000") when the
Criteria1:="<>32*" is NOT found.

When Criteria1 is found ... no problem.
When Criteria1 is not found ... it sucks to be me.

My Thanks to those that support these boards ... Kha


.

Relevant Pages

  • RE: A little Macro Help Please ...
    ... Criteria was NOT found ... ... The special cells xlcelltypevisible bit will check if the autofilter has ... Clear visible cells in Range A3:N2000. ... Clears visible cells in Range A3:N2000. ...
    (microsoft.public.excel.programming)
  • RE: A little Macro Help Please ...
    ... The special cells xlcelltypevisible bit will check if the autofilter has ... Clear visible cells in Range A3:N2000. ... Criteria not found ...Move on without clearing anything. ... Clears visible cells in Range A3:N2000. ...
    (microsoft.public.excel.programming)
  • Re: Error Message?
    ... If the number of visible cells in the first column is 1, ... > Is it possible to write a code that will generate an error message if ... I wrote a macro to do the filter based on ... > of the criteria used might not be available hence resulting in no ...
    (microsoft.public.excel.misc)
  • Re: Row search by criteria & copying data
    ... Filter on your citeria in column 2 ... to select all visible cells ... Ctrl-c to copy ... but some must be found by column name (criteria given). ...
    (microsoft.public.excel.programming)
  • RE: A little Macro Help Please ...
    ... Criteria not found ...Move on without clearing anything. ... Clears visible cells in Range A3:N2000. ...
    (microsoft.public.excel.programming)