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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

Hi Fiona,

Afraid I still don't follow -

Do you mean literally named 'cells', ie each of some 22000 names refers to a
SINGLE cell, if so are these scattered randomly within the range
"C2:O23000". I hope not because polling each of some 300k cells for their
'names' would take a long time.

If not could you give examples of typical named ranges and any clues where
to go searching for them, eg all 22000 names refer to single rows of 13
cells, eg "C2:O2", or single cells all within column C, eg "C2".

Do you mean many of the 22000 names refer to ranges on other sheets.

I'm confused reading "I'm trying to pull just over 100 rows" together with
"only about
1400 have names that start with PatRev. I would like to filter the entire
row"

Does that mean you want the 1400 PatRev rows included or excluded (latter as
implied in your OP). If included that's not '100', or if excluded that
doesn't tally with the 23k rows and implies 23000 - 1400 rows to be copied.

If eventually only some 100 rows of data will need to be extracted and
formats are not required, I anticipate it will be quicker NOT to use Excel's
filter method at all, do all with VBA.

Although not directly related to the current task, 22k names is indeed a lot
and can be highly resource intensive.

Regards,
Peter T



"Fiona" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

Hi Peter,

Thanks again for your time. I'll try and be clearer....

The worksheet shows financial data for just over 100 departments for
several different time frames (the time frames are in the columns C to
O). All the data is on one worksheet. Each department has the same
categories of data, one of those categories is Patient Revenue.
Patient Revenue is broken down into several rows of detail by (such as
Insurance, Medi-Cal, Medicare, etc.). The Patient Revenue is
subtotaled and each cell in that subtotal line is named using "PatRev"
then a dept code, and a column prefix. So for instance, PatRev6010C,
is the name for the cell that contains the Patient Revenue subtotal
for dept. 6010 and the time frame in column C. I'd like to pull out
all the patient revenue subtotal lines from all the departments and
copy them to a second worksheet - this would be just over 100 rows -
one for each department.

So, my defined cell names do refer to a single cell. That single cell
contains a subtotal. So, perhaps you're right that this will just be
too many named cells across the entire range to do a fast search. I
could limit the search to a single column - C - and if I find a cell
with the name starting with "PatRev" then just copy that entire row of
data to a new worksheet.

The code I listed in my earlier message does this but only by
searching each cell's value using the auto filter criteria line below:

rng.AutoFilter Field:=1, Criteria1:="=Pat Rev Total"

I'm trying to pull out a Pateint Revenue Subtotal line, and that line
has no unique name in the value of the cells (such as "Patient Reveue
SubTotal"). So, that is why I want to try and pull them out based on
the cell names starting with "PatRev". Uisng VBA to do it would be
great, I just don't know how to write the VBA code to tell the
autofilter to search the cell defined names, not the cell values.
Maybe it isn't possible?

Thanks again for your time and I hope I have clarified it.

Fiona

Hi Fiona,

If I follow this time it seems only necessary to check for PatRev names in
column-C cells, then get the whole row - right?

Also, if I understand, all these cells will include a SubTotal formula, that
will help a lot to restrict the search of named cells (see code).

In a new wb, first run SampleDataAndNames and see if the sample data & names
is something like what you are working with. Will be looking to copy data in
the yellow rows, but not the green rows which also have Subtotals and named
cells.

In GetFilteredData look at the SpecialCells line. This is like manually
select col-c, F5 > Special > Formulas tick only numbers & errors. Will that
work for you too, otherwise ignore and use the alternative commented rng.

Try both methods with bUseFilterMethod = True & Fasle

If True, fills column-P with True's in rows to filter, ie named cells in
col-c that start with PatRev. I've omitted the code to apply the filter and
copy to a new sheet but that will be very similar to the macro you posted
previously.

Probably, but depending on your data, the non-filter method will work just
as well if not better.

Sub SampleDataAndNames()
Dim b As Boolean
Dim i&, r As Range
Dim ws As Worksheet
Dim arr(1 To 23000, 1 To 1)

Set ws = Worksheets("Sheet4")
For i = 1 To UBound(arr)
arr(i, 1) = i
Next

For Each r In ws.Range("A123000").Columns
r.Value = arr
Next

With ws.Range("C123000")
For i = 100 To 23000 Step 100
.Rows(i).FormulaR1C1 = "=SUBTOTAL(9,R[-99]C:R[-1]C)"
b = Not b
If b Then
.Cells(i, 1).Name = "PatRev_C" & Right("0000" & i, 5)
.Rows(i).Interior.Color = vbYellow
Else
.Rows(i).Cells(1, 1).Name = "PatData_C" & Right("0000" & i,
5)
.Rows(i).Interior.Color = vbGreen
End If
Next
End With

End Sub

Sub GetFilteredData()
Dim b As Boolean
Dim i As Long, j As Long, cnt As Long
Dim lastRow As Long
Dim rng As Range, cel As Range
Dim wsFrom As Worksheet, wsTo As Worksheet

Dim bUseFilterMethod As Boolean ' for testing

Set wsFrom = ActiveWorkbook.Worksheets("Sheet4")
lastRow = wsFrom.Range("C" & wsFrom.Rows.Count).End(xlUp).Row

Set wsTo = ActiveWorkbook.Worksheets("Sheet2")

On Error Resume Next

'' Can we restrict search to formula cells like =SubTotal(
'' ( the 17 refers to numbers & errors in goto special Ctrl-F5)

Set rng = wsFrom.Columns("C:C").SpecialCells(xlCellTypeFormulas, 17)
If rng Is Nothing Then
MsgBox "no formulas in Col-C !"
Exit Sub
End If

''if can't use Specialcells will need to check all cells for names in
col-c
''23k cells will be slow, avoid if possible

'Set rng = wsFrom.Range("C1:C" & lastRow)


ReDim arridx(1 To rng.Count) As Long
i = 0
'' unnamed cells will error below, so do this with on
'' error resume next (already set above)
For Each cel In rng
b = LCase(Left(cel.Name.Name, 6)) = "patrev"
If b Then
i = i + 1
arridx(i) = cel.Row
cnt = cnt + 1
b = False
End If
Next

On Error GoTo 0

If cnt = 0 Then
MsgBox "no matching names found"
Exit Sub

ElseIf cnt < UBound(arridx) Then
ReDim Preserve arridx(1 To cnt)
End If

'bUseFilterMethod = True
If bUseFilterMethod Then
ReDim varr(1 To lastRow, 1 To 1)
For i = 1 To cnt
varr(arridx(i), 1) = True
Next
wsFrom.Range("P1:P" & lastRow).Value = varr
' set the filter on col-P for True,
' copy filtered range to new sheet
' remove the filter and clear contents in col-p
Else

ReDim arrCopy(1 To cnt, 1 To 15) ' 1 to 15 refers to cols a to o
With wsFrom.Range("a1" & lastRow)

For i = 1 To cnt
For j = 1 To 15
arrCopy(i, j) = .Cells(arridx(i), j)
Next
Next
End With
wsTo.Range("a1" & cnt).Value = arrCopy
End If

End Sub

Regards,
Peter T


"Fiona" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Peter,
>
> Thanks again for your time. I'll try and be clearer....
>
> The worksheet shows financial data for just over 100 departments for
> several different time frames (the time frames are in the columns C to
> O). All the data is on one worksheet. Each department has the same
> categories of data, one of those categories is Patient Revenue.
> Patient Revenue is broken down into several rows of detail by (such as
> Insurance, Medi-Cal, Medicare, etc.). The Patient Revenue is
> subtotaled and each cell in that subtotal line is named using "PatRev"
> then a dept code, and a column prefix. So for instance, PatRev6010C,
> is the name for the cell that contains the Patient Revenue subtotal
> for dept. 6010 and the time frame in column C. I'd like to pull out
> all the patient revenue subtotal lines from all the departments and
> copy them to a second worksheet - this would be just over 100 rows -
> one for each department.
>
> So, my defined cell names do refer to a single cell. That single cell
> contains a subtotal. So, perhaps you're right that this will just be
> too many named cells across the entire range to do a fast search. I
> could limit the search to a single column - C - and if I find a cell
> with the name starting with "PatRev" then just copy that entire row of
> data to a new worksheet.
>
> The code I listed in my earlier message does this but only by
> searching each cell's value using the auto filter criteria line below:
>
> rng.AutoFilter Field:=1, Criteria1:="=Pat Rev Total"
>
> I'm trying to pull out a Pateint Revenue Subtotal line, and that line
> has no unique name in the value of the cells (such as "Patient Reveue
> SubTotal"). So, that is why I want to try and pull them out based on
> the cell names starting with "PatRev". Uisng VBA to do it would be
> great, I just don't know how to write the VBA code to tell the
> autofilter to search the cell defined names, not the cell values.
> Maybe it isn't possible?
>
> Thanks again for your time and I hope I have clarified it.
>
> Fiona
>
>
>
>
>
>