Count last non blank column and apply to formula

[Dukesford]

Need a macro that counts from cell A4 until blank column. So lets say the blank column is L4 (11 nonblanks, 1 blank). I need to then offset that by 1 row down and column back so it is K5, I than need to apply that to the formula below and to: lastrow = cells (rows.count, instead of the 1 like it is below i need this to read 11 (because column K is the count of non blank columns = 11). The reason for this is because I have a Pivot table that gets wider and longer at least every month and I need the last column in the pivot table multiplied by $B$1.


Code:
Sub Button1_Click()
Dim lastrow As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Range("a5").Formula = "= """"K5""" * $b$1"
Range("a5").AutoFill Destination:=Range("a5:a" & lastrow)
End Sub

 

[Dukesford]

Ok I found it out incase anyone else could use this. I am using it on a pivot table that can get larger or smaller.

With Sheets("Company").Activate
With Range("b5", Range("b" & Rows.Count).End(xlUp))
x = .Cells(1).End(xlToRight).Offset(1).Address(0, 0)
lastrow = Cells(Rows.Count, 4).End(xlUp).Row
Range("b6").Formula = "=" & x & "*$c$1"
Range("b6").AutoFill Destination:=Range("b6:b" & lastrow)
lastrow = Cells(Rows.Count, 2).End(xlUp).Row
Range("c6").Formula = "=$C$2 * b6"
Range("c6").AutoFill Destination:=Range("c6:c" & lastrow)

Range("b5").End(xlDown).Select
Selection.Copy
Range("c3").Select
Selection.pastespecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("c5").End(xlDown).Select
Selection.Copy
Range("c4").Select
Selection.pastespecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
End With