subtotal function

[KATHYSPEERS]

I have a file that when the data is pulled in, it can vary in how many rows there are. Is there a way to say if this is the first blank row to put the subtotal formula in it, and after that is done- to format it so that all of the cells with info are formatted with borders? Thanks

 

[Smitty]

If you record a macro adding the formula and formatting you want, you can post the resulting code here and someone can make it dynamic for you.

 

[KATHYSPEERS]

Here is the macro

ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 58
ActiveWindow.ScrollRow = 72
ActiveWindow.ScrollRow = 83
ActiveWindow.ScrollRow = 95
ActiveWindow.ScrollRow = 106
ActiveWindow.ScrollRow = 113
ActiveWindow.ScrollRow = 115
ActiveWindow.ScrollRow = 120
ActiveWindow.ScrollRow = 122
ActiveWindow.ScrollRow = 126
ActiveWindow.ScrollRow = 128
ActiveWindow.ScrollRow = 133
ActiveWindow.ScrollRow = 135
ActiveWindow.ScrollRow = 136
ActiveWindow.SmallScroll Down:=6
Range("F188").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-186]C:R[-1]C)"
Range("G188").Select
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 104
ActiveWindow.ScrollRow = 87
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 1
Range("A2").Select
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 75
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 98
ActiveWindow.ScrollRow = 107
ActiveWindow.ScrollRow = 118
ActiveWindow.ScrollRow = 129
ActiveWindow.ScrollRow = 136
ActiveWindow.ScrollRow = 137
Range("A2:J187").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub

The number of rows will fluxuate

 

[Smitty]

THis will give you a dynamic subtotal:

    Dim LR As Long
        LR = Cells(Rows.Count, "F").End(xlUp).Row
        Cells(LR + 1, "F").Formula = "=SUBTOTAL(9," & "F1:F" & LR & ")"

You can use LR to do your formatting too.

Note in your code all of the ActiveWindow.ScrollRow = 33 actions are unnecessary and can be deleted.

HTH,