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...

 

[Joe4]

Try this:

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

 

[RunTime91]

Beautiful!!

Thanks Joe ~!

A quick question if I may ~ Why is it (or why does it seem to be) so much more difficult to AutoFill across columns as opposed to down rows

A couple of my attempts came close to what you have here - but close in this case was still miles away -

Based on what I use for AutoFill for down rows I would have never come up with your solution

So, My Thank You - is truly an understatement

 

[Joe4]

You are welcome.

Quite frankly, auto-fill is often unnecessary in VBA. If you are just copying the same formula or value across a whole range of cells, you can just apply that formula/value to the whole range at once. Excel VBA gives you the power to do that. I find the auto-fill code a little clunky anyway, and this other way makes more sense to me (and I find it easier to follow).

 

[RunTime91]

Joe, your explanation is as good as your solution...

Last question to tie both together (at least for me)

Your solution is, as you mentioned, easy to follow - there is one part that, if you could further clarify would be immensely helpful

This part of your code:

.Range(Cells(1, 1), Cells(1, lc)).Formula = "=Column()"

What exactly is the: .Formula = "=Column()" extension doing to the range object?

Based on the next line of code if appears to be filling in and storing the numbers from 1 to lc - if I'm correct - how is it doing that?

Thank You Joe... (I promise - last question)

 

[Joe4]

I think this will clarify things for you a bit:

Go into any cell on your Excel worksheet, and enter the formula:
=Column()
and see what it returns.

What it is returning is the column number for the cell that the formula in placed in. So, if the formula is in coilumn A, it will return 1, if it is in column B, it will return 2, etc.

So all that first line of code is doing is placing the formiula "=Column()" in all those cells.
We could leave it that way, if we want, and it would return the correct values.
However, if we want to convert those formulas to hard-coded values, we can do that with the next line, which tells it to set the value equal to whatever value is currently being returned in that cell.

Hope that clarifies things for you!

 

[RunTime91]

Wow!!! Nope ~ I never knew that!

And Yep ~ that clarifies it!!

I do believe when it comes to filling in columns - I now know how to fish ~

Joe - Thanks isn't enough!!

But Thank You, so very much!!

 

[Joe4]

You are welcome!
I am glad to see that you are taking an interest in not just getting answers, but learning and understanding it. I love when people do that!

As you might have already guessed, there is also a similar ROW function, that will return the row number. It can be a great way to insert record counters down a column. And if it doesn't start right at row 1, you can simply subtract a number from it.

For example, if you wanted to start a count at row 3, just place this formula in cell A3 and copy down:

=ROW()-2

 

[RunTime91]

Yep - I think I got it...

I changed this:

Application.ScreenUpdating = False


    With Sheet1
      .Columns("A:B").Insert
        .Range("A1") = "Number"
          .Range("B1") = "Name"
            .Range(("C1"), .Range("C1").End(xlToRight)).Copy
              .Range("B2").PasteSpecial Transpose:=True
            Lr = .Range("B1000").End(xlUp).Row
          .Range("A2") = 1
        .Range("A2").AutoFill .Range("A2:A" & Lr), xlFillSeries
      .Range("A1").Select
    End With


  Application.ScreenUpdating = True

To This:

Application.ScreenUpdating = False
  
    With Sheet1
      .Columns("A:B").Insert
        .Range("A1") = "Number"
          .Range("B1") = "Name"
            .Range(("C1"), .Range("C1").End(xlToRight)).Copy
              .Range("B2").PasteSpecial Transpose:=True
            Lr = .Range("B1000").End(xlUp).Row
          .Range(Cells(2, 1), Cells(Lr, 1)).Formula = "=Row()-1"
        .Range(Cells(2, 1), Cells(Lr, 1)).Value = .Range(Cells(2, 1), Cells(Lr, 1)).Value
      .Range("A1").Select
    End With
  
  Application.ScreenUpdating = True

I think I just caught a nice rainbow trout

You're awesome Joe!!

Thank You - Thank You - Thank You...

 

[Peter_SSs]

Just back to your earlier question:

Why is it (or why does it seem to be) so much more difficult to AutoFill across columns as opposed to down rows

I think it is a case of "seems" and it appears to be because of a little confusion perhaps between AutoFill, Fill Series, FillRight/FillDown etc and what you need to specify.

Here are a couple of "fill" ways to fill the numbers across the columns per your first post. I have assumed that you have established the value of Lc

Using Fill -> Series

.Range("A1") = 1
.Range("A1", .Cells(1, Lc)).DataSeries Rowcol:=xlRows, Type:=xlLinear, Step:=1 'Type & step determine the pattern of numbers

Using AutoFill

.Range("A1:B1") = Array(1, 2) 'Need at least 2 cells (values) to establish the pattern of numbers
.Range("A1:B1").AutoFill Destination:=Range("A1:J1")

If you were filling down a column, the codes would be of the same form and complexity. That is, no easier.



.. 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 & ")"))