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

 

[dudewheresmyvba]

Yes Joe4 it works nicely for that one. If you could help, I am having trouble with doing the same for the second set.

Range("ActiveCell" & "ActiveCell.Offset(0, 5)").AutoFill Range("ActiveCell" & "ActiveCell.Offset(0, 5)" & Range("ActiveCell.Offset(0,-1)").End(xlDown).Row)

/edited

Which is laughably wrong.

 

[Joe4]

Sometimes, I find it easier to break things up into multiple lines so it is easier to see what is going on. Shorter is not always better if you cannot follow what is going on, or won't remember what you did come next year when you are asked to support or change it.

So, I would use variables and do it like this:

    Dim lastRow As Long
    Dim nextRow As Long
    Dim lastRow2 As Long
    
'   Autofill set 1
    lastRow = Range("F1").End(xlDown).Row
    Range("H2:L2").AutoFill Range("H2:L" & lastRow)
    
'   Autofill set 2
    nextRow = Range("F" & lastRow).End(xlDown).Row
    lastRow2 = Range("F" & nextRow).End(xlDown).Row
    Range("G" & nextRow & ":L" & nextRow).AutoFill _
        Range("G" & nextRow & ":L" & lastRow2)

 

[dudewheresmyvba]

Ok almost there. My second set also has headers so how do I offset it one more row down?

 

[Joe4]

Just add one to the next row calculation.

nextRow = Range("F" & lastRow).End(xlDown).Row + 1

 

[dudewheresmyvba]

Wonderful. Its working smoothly now. Thanks for all your help!

 

[Joe4]

You are welcome!