I use a filter to sort data, then use a macro for data calculation. Ho
can I tell the macro to always choose the first 10 rows for th
calculation, regardless of row numbers

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

Hi "dflorine " ,
Depending on your formula you can use ranges that put
addresses into double quotes, or you can use INDIRECT.
If you posted your formula you would get more specific help.,
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"dflorine " wrote in message ...
I use a filter to sort data, then use a macro for data calculation. How
can I tell the macro to always choose the first 10 rows for the
calculation, regardless of row numbers?

You could mark the rows, and use the marked rows in a SUMIF or
SUMPRODUCT formula, e.g. =SUMIF(I:I,"X",E:E)

or find the row of the tenth item, and build a SUBTOTAL formula in a macro.

'=======================================
Sub MarkVisRows()
Dim rng As Range
Dim c As Range
Dim i As Integer
Set rng = ActiveSheet.AutoFilter.Range
i = 1
Columns("I").ClearContents
For Each c In rng.Columns(1).SpecialCells(xlVisible)
If c.Row 1 Then
If i 10 Then
Exit Sub
Else
c.Offset(0, 8).Value = "X"
i = i + 1
End If
End If
Next
Range("L1").Formula = "=SUMIF(E1:E" & r & ")"
End Sub
'=========================
Sub SubtotalTenRows()
Dim rng As Range
Dim c As Range
Dim i As Integer
Dim r As Long
Set rng = ActiveSheet.AutoFilter.Range
i = 1
Columns("I").ClearContents
For Each c In rng.Columns(1).SpecialCells(xlVisible)
If c.Row 1 Then
If i 10 Then
Else
r = c.Row
i = i + 1
End If
End If
Next
Range("K1").Formula = "=SUBTOTAL(9,E1:E" & r & ")"
End Sub
'=================================

dflorine < wrote:
I use a filter to sort data, then use a macro for data calculation. How
can I tell the macro to always choose the first 10 rows for the
calculation, regardless of row numbers?

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

I took this approach:
My data table has a single-row of headerinfo on Row3 - first data on row 4
downward
I like having a column(1) Inserted in most of my datatables when I use
Auto-Filtering
with a formula in A4 of =SUBTOTAL(3,$B$4:B4) and copied down to the end
of the
data. Label in A3 of "Seq #".

So that when I filter my Column A always gives me a sequential row count of
the visible
cells. Nice feature, huh..

In my Column C cell C1 I entered:

=SUBTOTAL(9,INDIRECT("C3:C"&MATCH(5,$A3:$A20,0)+2) ) and copied across
replacing in each copied cell the two "C"s to the appropriate column letter.
Once
Done it works like a charm.

Hope this helps!!!
It helped me,,,







"dflorine " wrote in message
...
I use a filter to sort data, then use a macro for data calculation. How
can I tell the macro to always choose the first 10 rows for the
calculation, regardless of row numbers?


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