row numbers

D

dflorine

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
 
D

David McRitchie

Hi "dflorine >" <<[email protected]> ,
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.,
 
D

Debra Dalgleish

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
'=================================
 
J

JMay

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,,,
 
Top