Inserting a variable into an Advanced Filter criteria field

[catlair]

Hi

By using the following I am able to count the number of lines that have
data:

iDataRows =
Application.WorksheetFunction.CountA(ActiveSheet.Columns(1))

For x = 1 To iDataRows
'Your processing code here
Next x

Then use the same info or data for this Advance Filter as a target cell
for after the "Range" as a "CriteriaRange" first cell data (+ 1) and
then calculating the end cell (+ 7), and copy into a cell down:

Range("A1:S100").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range( _
"A101:B107"), CopyToRange:=Range("A110"), Unique:=False

How do I use this "iDataRows" variable to perform this function?

I am not familiar at all with VB.

 

[Bob Greenblatt]

Hi

By using the following I am able to count the number of lines that have
data:

iDataRows =
Application.WorksheetFunction.CountA(ActiveSheet.Columns(1))

For x = 1 To iDataRows
'Your processing code here
Next x

Then use the same info or data for this Advance Filter as a target cell
for after the "Range" as a "CriteriaRange" first cell data (+ 1) and
then calculating the end cell (+ 7), and copy into a cell down:

Range("A1:S100").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range( _
"A101:B107"), CopyToRange:=Range("A110"), Unique:=False

How do I use this "iDataRows" variable to perform this function?

I am not familiar at all with VB.

Based on your explanation, I am totally confused about what you are trying
to do. Do you want a formula to be part of an advanced filter criteria? If
so, what are you trying to accomplish? Do you want the filtered data to show
only rows that have data in a certain column? Do you want to count the
number of rows that show up after the data is filtered?

 

[catlair]

Hi Bob,

Do you want a formula to be part of an advanced filter criteria?

= I am using the formula to capture the number of rows with data and
then assigning the 'iDataRows" name to that number/value. The formula
works and gives me, for example, a value of 100 rows which is assigned
to "iDataRows".

What are you trying to accomplish? Do you want the filtered data to show
only rows that have data in a certain column? Do you want to count the
number of rows that show up after the data is filtered?

= I hope to take that assigned value, 'iDataRows", and insert that
value to Range("A1:S100") where 'S100' is the number of rows i.e.
"iDataRows" value. "S" is not a variable. "100" value is.

Also, to add a few lines down to copy the filtered data i.e.
CopyToRange:=Range("A110"); where "A110" is "iDataRows" plus 10 i.e.
the variable value + 10.

Hope I am making some sense and this clarifies my objective. I could
you the spreadsheet to illustrate what I mean? Thanks for your
patience.

catliar

 

[Bob Greenblatt]

Hi Bob,

Do you want a formula to be part of an advanced filter criteria?

= I am using the formula to capture the number of rows with data and
then assigning the 'iDataRows" name to that number/value. The formula
works and gives me, for example, a value of 100 rows which is assigned
to "iDataRows".

What are you trying to accomplish? Do you want the filtered data to show
only rows that have data in a certain column? Do you want to count the
number of rows that show up after the data is filtered?

= I hope to take that assigned value, 'iDataRows", and insert that
value to Range("A1:S100") where 'S100' is the number of rows i.e.
"iDataRows" value. "S" is not a variable. "100" value is.

Also, to add a few lines down to copy the filtered data i.e.
CopyToRange:=Range("A110"); where "A110" is "iDataRows" plus 10 i.e.
the variable value + 10.

Hope I am making some sense and this clarifies my objective. I could
you the spreadsheet to illustrate what I mean? Thanks for your
patience.

catliar

Based on your explanation, I am totally confused about what you are trying
to do. Do you want a formula to be part of an advanced filter criteria? If
so, what are you trying to accomplish? Do you want the filtered data to show
only rows that have data in a certain column? Do you want to count the
number of rows that show up after the data is filtered?

Well, I'm still not sure what you are trying to accomplish. I don't
understand if you are trying to define a name in a worksheet, or trying to
write some code. If the latter, what is the code trying to accomplish?
You can use the subtotal function to count or total only visible rows. You
can make a self defining name that will automatically span a changing number
of rows. Something like:
=offset($a$1,0,0,counta($A:$A),countA($1:$1))

If this is in the refers to of a new on the worksheet, the reference will
automatically expand and contract as you change the number of rows and
columns.

 

[catlair]

Thanks, Bob! Will have to rethink my macro.

 

[catlair]

Hi Bob,

Sorry for any confusion. I am trying to pick a variable and insert it
into the Advance Filter Criteria Range. Not sure if that is possible.

catliar

 

[Bob Greenblatt]

Hi Bob,

Sorry for any confusion. I am trying to pick a variable and insert it
into the Advance Filter Criteria Range. Not sure if that is possible.

catliar

Sure it is possible. What I have been unable to understand is what you are
trying to do. What are you trying to filter? What is the criteria you are
trying to achieve? The criteria values can most certainly be the result of a
formula. Your VBA code can either evaluate the formula and insert the value,
or insert a formula which Excel will calculate at the time the data is
filtered. Once again, what are you trying to do?

 

[catlair]

HI Bob,

Hope to count the number of lines with data then using that information
in Advance Filer (or rather inserting that information) to indicate
where to copy the data extracted by Advance Filter.

catlair

 

[Bob Greenblatt]

HI Bob,

Hope to count the number of lines with data then using that information
in Advance Filer (or rather inserting that information) to indicate
where to copy the data extracted by Advance Filter.

Believe it or not, you still are not making yourself clear. Do you want to
count the number of rows in the database, or the number of rows resulting
from the advanced filter? If you are filtering in place, copying the result
will also copy the hidden rows.

I fail to see how knowing the number rows containing data has anything at
all to do with creating an advanced filter. If you need to define the size
(in number of rows) of the table to be filtered, then any of the self
defining formulas I have presented earlier should work fine.

Please try to explain what you are trying to do on a more global scale.
Like: "I have a data table that contains ______. I would like to filter
this data to show _____. I don't know how to define the filter so that only
_____ data containing ____ appears in the final result. "

If you think it would be easier for you, I'll be happy to look at your
workbook. We can then discuss your problem in more specifics. Feel free to
send it to me at the address below.