CountA - Dynamic Range - Macro

I recorded this macro based on a column with a drop down window in which data has been filtered.

------

Please Login or Register  to view this content.

-------

Now, it drops down to the end of the data, then one more,
executes the "counta" formula back up to the top and then
reports out the total.

I'd like to modify it so that it goes down to the end of the
data, regardless of it's length and executes counta from
there.

How do I dynamically setup the "countA" formula to
return the total of the column from current location (at
the bottom of the data) to one row down from the top?

So, for example, if the bottom of data is at row 37, the
counta would execute

=counta(c?r2:c?r37)

If it were at row 52 it would dynamically, regardless of
column execute:

=counta(c?r2:c?r52)

make sense?

Anyone?

Thanx.

There's no need to Select, that will only create inefficient code. Do you want the result in a cell or the Formula?

Try this

Please Login or Register  to view this content.

Thanx Roy.

I've included an attachment of what I have so far.

It's not working quite right.

Still, it's so close.

In playing with this,
I accidentally figured out
why a msgbox is probably
a good idea.

In selecting the range and
plugging in the total, in
doing so in the same column,
I find, it buries it in the display,
given the fact that it's filtering
the data.

Is your second post independent
of the first, but, same method
or is it intended to be incorporated
into the first, and if so, exactly
what would the resulting combination
look like, just to make sure I'm mirroring
your intent?

You don't need the other code.

If you want to use it on the filtered rows only then you need to change it slightly, also you are using a different column to the one that you said

Please Login or Register  to view this content.

Couldn't've done it without your input Roy.

I stuck the answer cell all the way over to
the right, in $IV1. Figured that way it
wouldn't get in the way of or overwrite
existing data.

Basically works the same way, once you
pick a range from the drop down list, click
on the first cell in the range and run
the macro. I assigned it to a smiley face
button on the toolbar as well.

If I missed something or something looks
out of place or could've been done better,
different, let me know.

Thanx.

I really don't understand why you need to insert a name or select the cells, then write the result to the sheet.

My code gave you the results without any of that.

The code works like this

Please Login or Register  to view this content.

Here's my code:

Please Login or Register  to view this content.

What I was hoping to get initially was a solution which could
be applied to ANY column - dynamically, something which I began
to specify in subsequent posts.

And something which, if not elegantly or efficiently, my code does.

Yours requires, your words, a modification of the code to apply
to a different column.

In the attached spreadsheet, I can pick drop down data from ANY
column, then click on the uppermost cell of the data, run my macro
and get a total of said data.

Your macro only works on column B, unless I modify the code.

Your macro does not require selection or a range name, but ONLY
works on one column, unless it is modified.

As I previously explained, and further elaborate on, the user I am
providing this code for, is not macro or vba literate, and in dealing
with MANY columns of multiple data sets, to modify the code for
each column is more work than I could or they would reasonably
expect - that would be equally inefficient - no?

I think this could prove to be useful for users in similar situations.

My code or your code, doesn't matter to me, but, it should,
for my purposes be able to:

1) Determine the total of ANY column
2) Not require the code to be modified from column to column
and
most efficiently
3) Not require selection or range name

First and foremost steps 1 and 2

My code does 1 and 2

Your code gives the total of a specific column, requires modification of
the code for any other column, but does not require selection of
cells or the naming of a range.

My code determines the total of any column, does not require
modification of the code for any other column, but DOES require
selection of cells and the naming of a range.

To reconcile your conditions with my request would mean your code
does what mine does without the selection of cells or the naming of
a range.

Doesn't mean my code is better than yours, means my code does
what I'd like it to do, but not as efficiently as your code, or as you
would like, or think it ought to.

Agreed?

Best of all worlds would be to be able to be prompted for any
column, or range, or named range, and have a total returned
based on your method.

Better yet, it could then either prompt the user for a location
in which to store the data in, perhaps a format such as:

Cell1---Cell 2
Item:--Total

or automatically store the results in a specific coded location(s)
same or different worksheet/workbook.

That may be getting well ahead of the game. For now, just
the return of a total from ANY column is probably enough.

(Spreadsheet attached
with yours and my code)

The modification needed would be a simple input box to request the column number, but carry on your way. i was only trying to help you code efficiently!