I have a worksheet that is updated daily by a pivot table. The line count changes daily. I am updating a macro that will filter the data, sort it by cost, and then find the percentage of cost based on the total population. How do I get VB to tell me how many lines are in the worksheet, (subtracting one for the header) and then stick that value into my formula?
Here is the code leading up to this point.
'sort sheet by descending extended cost
Range("E1").Select
Selection.sort Key1:=Range("E2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'Paste highest value in Cum Total Column
Range("E2").Select
Selection.Copy
Range("H2").Select
ActiveCell.PasteSpecial
'add formula to tally up Cum Cost all the way down (column H)
Range("H3").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+RC[-3]"
Dim i As Long
Dim z As Range
i = [F65536].End(xlUp).Row
[H4] = "=R[-1]C+RC[-3]"
[H4].Copy Range("H4:H" & i)
Set z = Sheets("All But CONS").Range("H4:H" & i)
I've been asked to use the following formula: H2/$H$###
where ### stands for the line count.
Any good ideas?
Here is the code leading up to this point.
'sort sheet by descending extended cost
Range("E1").Select
Selection.sort Key1:=Range("E2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'Paste highest value in Cum Total Column
Range("E2").Select
Selection.Copy
Range("H2").Select
ActiveCell.PasteSpecial
'add formula to tally up Cum Cost all the way down (column H)
Range("H3").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+RC[-3]"
Dim i As Long
Dim z As Range
i = [F65536].End(xlUp).Row
[H4] = "=R[-1]C+RC[-3]"
[H4].Copy Range("H4:H" & i)
Set z = Sheets("All But CONS").Range("H4:H" & i)
I've been asked to use the following formula: H2/$H$###
where ### stands for the line count.
Any good ideas?