AutoFill Across Columns

[RunTime91]

Hard to believe I am here for this, but.... here I am

Just trying to AutoFill xlFillSeries from Column A to LastColumn

Here is where I'm at (I think I'm close?)

Application.ScreenUpdating = False
  
    With Sheet1
      Lc = .Cells(1, .Columns.Count).End(xlToLeft).Column
        .Rows("1:1").Insert
          .Range("A1") = 1
        .Cells(1, Lc).AutoFill , xlFillSeries
      .Range("A1").Select
    End With
  
  Application.ScreenUpdating = True

What I'm trying to accomplish is to simply put numbers across the tops of my column names...(changing the sheet to R1C1 wont' work for this project.)

And, as I'm sure most of you reading this will know - I'm getting an AutoFill Method of Range Error

Help?

Thanks everyone...

 

[RunTime91]

Wow ~ This thread has turned into quite the tutorial for how to fill accross columns.

Joe & Peter ~

Your expertise, insights and explanations will/(should) keep this thread being viewed for years to come.

Especially by folks whom, like myself, come here looking not for just a solution, but for a better understanding of how and why that solution works.

Sites like this work only because of experts like yourselves and I for one can't thank you enough for sharing your expertise with 'rooks' like myself.

(some years ago I made a MsgBox pop-up that said 'Hello World!' and now everybody thinks I'm an Excel Guru) lol.

Joe & Peter ~ Thank You (and the rest of this community) again - Very Much for helping me to keep them believing that!

RT91

 

[Peter_SSs]

I for one can't thank you enough for sharing your expertise with 'rooks' like myself.

You just did.
And thank you for your other kind words.

 

[Rick Rothstein]

.. and just for the record here is a way without needing to put anything in any of the cells first. (Though I'm not particularly recommending this as a 'better way')

.Range("A1").Resize(, lc).Value = Application.Transpose(Evaluate("row(1:" & lc & ")"))

Just to follow up on this method, if you know that the last column will never be more than some fixed value (say, 700 columns), you can use this simple code line letting the actual lc inside the Resize property call control the final output...

.Range("A1").Resize(, lc) = [COLUMN(A:ZZ)]

I used Column ZZ (which is the 702nd column) because I think ZZ looks "nicer" in the formula than ZX (which is 700th column). Anyway, the idea is to set the last column to a column letter designation which is always going to be greater than the largest column you will ever need to fill in.