CODEWITHSUNDEEP
excelvba
mango
mango

Reputation: 5636

programmatically auto-fill one column

My aim should be easy enough. just auto-fill one column to a specific row:

Range("A3:C3").Select
Selection.AutoFill Destination:=Range("A3:C" & RowCount)

That code block works perfectly. But what i want to is to auto-fill the three columns individually because there are different RowCounts for each. I tried:

Range("A3").Select
Selection.AutoFill Destination:=Range("A3:A" & RowCount)

but I get Run-time error '1004': Method 'Range' of object '_Global' failed

Upvotes: 1

Views: 2012

Answers (1)

chuff
chuff

Reputation: 5866

The code below worked without problem for me. The only way I was able to get it to fail with the error message you encountered was by setting rowcount to a value that produced an invalid address (like "A3:A0"). 

  Sub FillIt()
      Dim RowCount As Long
      RowCount = 10
      Range("A3:A4").AutoFill Destination:=Range("A3:A" & RowCount)
  End Sub

There are several options for how the .Autofill method will fill the series.

The code above assumes that the first two cells of the desired range contain the first two values of the series. For example, if A3 = 1 and A3 = 2, then Excel assumes that you want a series of consecutive integers 1, 2, 3.... If the values are 1 and blank, then the resulting series will be 1, blank, 1, blank, etc.

How the series will be filled can be specified with the optional "Type" paramater. The fill method in the code is equivalent to setting Type:=xlFillDefault. There are several other fill types available, such as xlFillSeries, xlFillCopy, and xlFillLinearTrend. For a full listing of these, see Microsoft's reference page.

Upvotes: 4

Related Questions