I'd like to create a macro that will do an autofilter of my worksheet
filtering out all cells that have defined names that start with
"PatRev". Does anyone know if this is possible and if so could you
please send some sample macro code? I've searched for an answer to
this with no success.

Thanks!

Maybe you could just hide those rows (different than autofiltering).

Option Explicit
Sub testme01()
Dim nm As Name

On Error Resume Next
For Each nm In ActiveWorkbook.Names
If Left(LCase(nm.Name), 6) = LCase("PatRev") Then
nm.RefersToRange.EntireRow.Hidden = True
End If
Next nm
On Error GoTo 0

End Sub




Fiona wrote:

I'd like to create a macro that will do an autofilter of my worksheet
filtering out all cells that have defined names that start with
"PatRev". Does anyone know if this is possible and if so could you
please send some sample macro code? I've searched for an answer to
this with no success.

Thanks!

--

Dave Peterson

Just for fun here's a non VBA approach -

Select cell B1 << important
Define the following name
Name: GetName
Refersto: =GET.DEF(ADDRESS(ROW(),COLUMN()-1,1,0))

Verify =GetName returns the name of a cell offset one to left, eg name A1
and put =GetName in B1.

Now to filter out rows in which named cells in a column start with "PatRev"

In a column one to the right of the Named cells you want to filter (if
necessary insert a new column), starting in row2 or lower enter following
and copy down (or select all cells and Ctrl-Enter)

=OR(ISERROR(getName),LEFT(getName,6) = "PatRev")

Apply an Autofilter, in the Custom field -
Does not equal : True

In the refersto, you could change 'COLUMN()-1' to an absolute cell ref, eg
$A$1, that contains the column-number of the column of cells whose names you
want to filter. That would avoid the necessity of pre-defining the helper
column offset, in this case 1.


Personally I'd go with Dave's macro !

Regards,
Peter T


"Fiona" wrote in message
ups.com...
I'd like to create a macro that will do an autofilter of my worksheet
filtering out all cells that have defined names that start with
"PatRev". Does anyone know if this is possible and if so could you
please send some sample macro code? I've searched for an answer to
this with no success.

Thanks!

I posted an unnecessarily complicated formula (had copied wrong one from
various formulas I tried) -

=OR(ISERROR(getName),LEFT(getName,6) = "PatRev")

Simply -
=LEFT(getName,6) = "PatRev"
Autofilter Custom : does not equal : True

Peter T

"Peter T" <peter_t@discussions wrote in message
...
Just for fun here's a non VBA approach -

Select cell B1 << important
Define the following name
Name: GetName
Refersto: =GET.DEF(ADDRESS(ROW(),COLUMN()-1,1,0))

Verify =GetName returns the name of a cell offset one to left, eg name A1
and put =GetName in B1.

Now to filter out rows in which named cells in a column start with
"PatRev"

In a column one to the right of the Named cells you want to filter (if
necessary insert a new column), starting in row2 or lower enter following
and copy down (or select all cells and Ctrl-Enter)

=OR(ISERROR(getName),LEFT(getName,6) = "PatRev")

Apply an Autofilter, in the Custom field -
Does not equal : True

In the refersto, you could change 'COLUMN()-1' to an absolute cell ref, eg
$A$1, that contains the column-number of the column of cells whose names
you
want to filter. That would avoid the necessity of pre-defining the helper
column offset, in this case 1.


Personally I'd go with Dave's macro !

Regards,
Peter T


"Fiona" wrote in message
ups.com...
I'd like to create a macro that will do an autofilter of my worksheet
filtering out all cells that have defined names that start with
"PatRev". Does anyone know if this is possible and if so could you
please send some sample macro code? I've searched for an answer to
this with no success.

Thanks!

Thanks Dave and Peter for your replies. I tried Dave's suggestion,
but I think due to the size of my worksheet (about 23,000 rows) it
just took a long time to run. The autofilter works really fast when I
do it on the cell's contents. I use the code below (which I got from
one of the excel groups) to copy all rows with the words "Pat Rev
Total" in the cells value into a new worksheet called PatRevResult. I
really want to modify it so I can do the autofilter based on the
cell's defined name starting with "PatRev" (as that will pull some
additional rows) , but I just can't get it to work. Can anyone show
me how I could modify this to do that? Thanks! :

Sub Copy_With_AutoFilter_patRev()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim rng2 As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set WS = Sheets("Sheet1") 'Name of the worksheet with the data
Set rng = WS.Range("A1:O" & Rows.Count)
WS.AutoFilterMode = False
On Error Resume Next
Application.DisplayAlerts = False
Sheets("PatRevResult").Delete
Application.DisplayAlerts = True
On Error GoTo 0

rng.AutoFilter Field:=1, Criteria1:="=Pat Rev Total"
Set WSNew = Worksheets.Add
WSNew.Name = "PatRevResult"

WS.AutoFilter.Range.Copy
With WSNew.Range("A1")
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With

WS.AutoFilterMode = False

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

There may be several methods to speed things up significantly, though
nothing will work as fast as filtering pre-existing values in a column which
themselves do not require any form of calculation, ie if they are constants.

Could you clarify more clearly what you are trying to filter, is it as I
understood single named cells, each of which is in a single column. If so
will it always be the same column (if so which) or will you need to cater to
filter named cells in different columns. Or, are you looking to filter the
row if any named cells matching the criteria exist in any cell in the entire
row.

Will named cells ever exist below or to the right of the UsedRange.

In your 23k cells in the column (or 23k rows), roughly how many are named,
and of these roughly how many will start with "PatRev". Again from your OP I
understand these are the ones to filter out (exclude).

The macro you posted does essentially something that can be done quite
easily manually. I notice you are copying - pastespecial both values and
formats to another sheet, are the formats important, if not a completely
different method might be better.
I'm not sure what the .PasteSpecial Paste:=8 does.

FWIW, though I posted the defined formula method for curiosity, it might
work very well for your purposes with a bit more tweaking.

Regards,
Peter T


"Fiona" wrote in message
oups.com...
Thanks Dave and Peter for your replies. I tried Dave's suggestion,
but I think due to the size of my worksheet (about 23,000 rows) it
just took a long time to run. The autofilter works really fast when I
do it on the cell's contents. I use the code below (which I got from
one of the excel groups) to copy all rows with the words "Pat Rev
Total" in the cells value into a new worksheet called PatRevResult. I
really want to modify it so I can do the autofilter based on the
cell's defined name starting with "PatRev" (as that will pull some
additional rows) , but I just can't get it to work. Can anyone show
me how I could modify this to do that? Thanks! :

Sub Copy_With_AutoFilter_patRev()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim rng2 As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set WS = Sheets("Sheet1") 'Name of the worksheet with the data
Set rng = WS.Range("A1:O" & Rows.Count)
WS.AutoFilterMode = False
On Error Resume Next
Application.DisplayAlerts = False
Sheets("PatRevResult").Delete
Application.DisplayAlerts = True
On Error GoTo 0

rng.AutoFilter Field:=1, Criteria1:="=Pat Rev Total"
Set WSNew = Worksheets.Add
WSNew.Name = "PatRevResult"

WS.AutoFilter.Range.Copy
With WSNew.Range("A1")
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With

WS.AutoFilterMode = False

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

Hi Peter,

Thanks for your reply. Here are the answers to your questions:

I'm trying to pull just over 100 rows from the spreadsheet that has
about 23k rows. Each of these rows has data I want pulled out in
columns A to O. The named cells always start at column C and go thru
Column O (Column A and B are titles). These named cells are the names
of various subtotals. The total # of named cells I have on the sheet
is about 22,000 (quite a lot!). Of those named cells, only about
1400 have names that start with PatRev. I would like to filter the
entire row (really copy it to a new spreadsheet) if the cell in
column C has a name that starts with "PatRev".

You asked if the formats were important for the copy paste - no they
aren't.


I'm not sure what the .PasteSpecial Paste:=8 does.

Paste:=8 will copy the columnwidth in Excel 2000 and higher

I hope I hope I've answered all your questions. Thank you for your
help!

Fiona