CODEWITHSUNDEEP
excelvbaautofill
Colton
Colton

Reputation: 35

Excel or VBA Issue? Range.Autofill Method sometimes starts where I don't want it to

I'm new to VBA, and I'm trying to make a Form Control Button that -when clicked- autofills a very complex formula down a column until it reaches the end of a dynamic pivot table. The pivot table is grabbing data from another sheet/tab called "Data Import" consisting of assets and their respective depreciation info.

So, this works beautifully when my Data Import tab has maybe 100 assets or more, but when there are less than that, it somehow starts applying the formula above where I want the range/autofill to start. To elaborate, if I have like 7 assets in my pivot table (7 rows), and I want the formulas to autofill range(O15:O400), it starts autofilling at (O5:O400) <--Maybe O400.

The code:

Sub AutoFill()
  Dim k As Long

'To get a number rows taken up by assets
  k = Range("A15", Range("A15").End(xlDown)).Rows.Count
'To apply AutoFill at specified range
  Range("O15").AutoFill Destination:=Range("O15:O" & k)

End Sub

I'm using column A because it's a primary key for the assets, thus, there will always be primary key.

Thanks in advance!

Upvotes: 0

Views: 99

Answers (1)

Scott Craner
Scott Craner

Reputation: 152525

You are counting the number of cells in a range starting at row 15 but then expecting that count to be the last row.

If the End hits row 30 then Rows.Count will be 16 and you fill will only fill O15:O16.

If the Last row is 25 then the count would be 11 and the range to fill would be O11:O15:

Sub AutoFill()
  Dim k As Long
  With Worksheet("Sheet1") 'Change to your sheet
      'To get a number rows taken up by assets
      k = .Range("A15").End(xlDown).Row
      'To apply AutoFill at specified range
      .Range("O15").AutoFill Destination:=.Range("O15:O" & k)
  End With
End Sub

Upvotes: 2

Related Questions