Hi Excel Forum,

Hope you can help.

I am working with filtered data that will be copied to anothe
worksheet (via a macro procedure). The first row of data is Row Numbe
4, the column is C.

How can I automate the calculation of a total to be placed on th
worksheet (after the copying process) without knowing the final / las
Row Number of copied filtered data -- this will vary: Row Number canno
be fixed/ hard-coded?

How can I sum the values from Row Number 4 to whatever Row Number hold
the last data and place a total two rows below the last row of data?

Please provide a working example.

Thank you
QT

--
Message posted from http://www.ExcelForum.com

The question is not entirely clear. I assume that you are doing th
following:
+ DataFilterAutoFilter
+ selecting an AutoFilter value
+ selecting/copying visible cells
+ pasting this to another worksheet

You want to know how to find out the last row in the worksheet you jus
pasted to.

There are at least 3 ways.
1) my favorite would be to use CurrentRegion in the new worksheet
Set myRange = Range("C4").CurrentRegion
LastRow = myRange.Cells(myRange.Cells.Count).Row
2) quick and dirty, works only if there are no blanks in column C
LastRow = Range("C4").End(xlDown).Row
3) will find the absolute last row ever used in this worksheet
LastRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row

If my assumptions are wrong, and you want to find out what was the las
row number in the original table, then you would do something lik
this:

'set LastTableRow equal to the last row of data in the datatable
LastVisibleRow = FirstTableRow
For i = FirstTableRow to LastTableRow
if cells(i, "C").entirerow.hidden = False then LastVisibleRow = i
Next Ro

--
Message posted from http://www.ExcelForum.com

Hi MSP77079,

Thank you for reply. Apologies, if the question was not clear.

The data is being filtered by Advanced Filter using different criteria


It is not that I want to know how to find out the last row in th
worksheet I just pasted to, but rather, how to automate the placing o
totals on that worksheet to avoid them being overwritten each time th
number of rows of filtered data changes. I want to place the Formul
Totals two rows after the last row of filtered data is pasted. Th
Formula for the totals will be in the Macro. I want to avoid th
totals being overwritten each time the number of copied rows/ record
changes. I do not want to manually shift the rows up or down, as th
case may be each time.

Based on the above which of your solutions do you recommend and ho
would I actually tack on the placing of the totals to one of th
solutions.

Thank you.
QT

--
Message posted from http://www.ExcelForum.com