Autofill

[dudewheresmyvba]

Ok, so. I have my range selected through

Range("G1").Select
Selection.End(xlDown).Select
Range(ActiveCell, ActiveCell.Offset(0, 5)).Select

because it has changing number of rows.

I want to have those cells (G:L) autofilled but am having some difficulty finding the proper way to do it. If there is just 1 more line I need to add that would be super but if I need to use Dim and named ranges that would be fine too, but I don't know how to do that on my own yet.

Thanks

 

[Joe4]

One way:

    Dim lastRow As Long
    lastRow = Range("G1").End(xlDown).Row
    Range("G1:L" & lastRow).Select

 

[MARK858]

Another way to try (if I haven't made any typos on my phone)

Range("G1:M1").Autofill Range("G1:M" & Range("G" & Rows.Count).End(xlUp).Row)

 

[dudewheresmyvba]

@Joe4
That seems to just select that range but not autofill anything? Am I missing something there?

@MARK858
Issue with this (other than me having headers) is that I have a second set of data 4 rows below this selected set which is why I am using .End(xlDown)
I am going to do some trial and error to see if I can get it to work unless you beat me to it.

 

[Joe4]

I was just showing you how to get your range, not the Autofill piece.
I think we need more information for that.
If you are using column G to determine that last row, then I don't think you are autofilling that column (as it is already populated).
So then are you just autofilling columns H through L?
Does row 1 have formulas in all those columns to be autofilled down?

If my assumptions above are correct, then just use:

    Dim lastRow As Long
    lastRow = Range("G1").End(xlDown).Row
    Range("H1:L1").AutoFill Range("H1:L" & lastRow)

or just

    Range("H1:L1").AutoFill Range("H1:L" &  Range("G1").End(xlDown).Row)

 

[dudewheresmyvba]

I think all that I need is for it to match the number of rows in column F.
Column G to L all have less rows than A thru F.
H, I, K, and L have formulas that need to be autofilled but G and J are filled with other macros (after this sub runs).
Row 1 is headers so this would begin at row 2.

 

[Joe4]

Then try:

    Range("H2:L2").AutoFill Range("H2:L" &  Range("F1").End(xlDown).Row)

 

[dudewheresmyvba]

And since I have a second set of data beneath this set some functions like Mark's wont work. Sorry I didn't specify that in the OP.

I figured I could just modify the autofill code to fit the second set as well because when doing it not in vba all you do is click the cross and it fills correctly.

 

[MARK858]

@MARK858
Issue with this (other than me having headers) is that I have a second set of data 4 rows below this selected set which is why I am using .End(xlDown)
I am going to do some trial and error to see if I can get it to work unless you beat me to it.

As a guess...

Range("G1:L1").Autofill Range("G1:L1").Resize(5, 1)

Edit: Sorry Joe4 I missed post #7

 

[Joe4]

And since I have a second set of data beneath this set some functions like Mark's wont work. Sorry I didn't specify that in the OP.

As long as you have blank rows between your data sets, I believe the code I posted up in post #7 should do what you want.