Auto fill macro for 2 x cols only .

[Sunline]

Hello all ,
can someone point me to a simple macro that can auto fill 2 x cols at once , or one if that's not possible .

I have data in cols F and G and just want to fill down to end of sheet just as a normal auto fill function would operate .

Have looked at previous thread answers but all were not basic enough , thanks .

 

[igold]

Perhaps this...

Sub fill()

    Dim lRow As Long, nSrow As Long, vlRow As Long, r As Long, lst As Boolean
    
    Application.ScreenUpdating = False
    r = 2
    vlRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    lRow = ActiveSheet.Range("F" & r).End(xlDown).Row - 1
    Range("F" & r).AutoFill Range("F2:F" & lRow), Type:=xlFillDefault
    Range("G" & r).AutoFill Range("G2:G" & lRow), Type:=xlFillDefault
    Do Until lst = True
        nSrow = lRow + 1
        lRow = ActiveSheet.Range("F" & lRow + 1).End(xlDown).Row - 1
        r = lRow
        If lRow > vlRow Then
            lRow = vlRow
            lst = True
        End If
        Range("F" & nSrow).AutoFill Range("F" & nSrow & ":F" & lRow), Type:=xlFillDefault
        Range("G" & nSrow).AutoFill Range("G" & nSrow & ":G" & lRow), Type:=xlFillDefault
    Loop
    Application.ScreenUpdating = True

 

[Sunline]

Hi Michael ,
Your macro increases my sheet to 1.5 millions rows and is giving 2 results for each number and text only . Doesn't fill down all the way .
The number is putting the date under each number .
The text is giving a result but as mentioned only 2 x results .
Thanks for looking at this .

 

[Sunline]

igold yours is giving me compile error , expected end sub .
Thanks .

 

[igold]

@Michael M your code worked great. But then it failed. Then it worked great, then it failed.

Not sure what was happening, but obviously I missed the boat. That is great.

 

[igold]

Add end sub to the bottom of my code I must have missed it when I copied it over...

 

[Michael M]

changed to find last row

Sub fild()
dim lr as long
lr = cells(rows.count,"A").end(xlup).row
With Range("F2:G" & lr)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
End With
End Sub

 

[Sunline]

Michael yours is still the same as before but definitely didn't add 1.5 million rows . Still giving only 2 results for each , the date thing was my formatting which I have corrected to numbers .

igold I'm getting error Autofill method of range class failed , its highlighting in yellow at bottom where code is

Range("F" & nSrow).AutoFill Range("F" & nSrow & ":F" & lRow), Type:=xlFillDefault

and yours filled the top or first name and number correctly . This did autofill . for the second data entry it filled twice only and for all the rest also . All good progress for me .
Thanks guys .

 

[Michael M]

My code filled as required, based on your sample data.
So I'd suggest that there is a miscommunication somewhere in the sample data OR your requirements.
I hope you have reached a positive conclusion from other posts...

 

[igold]

My code did test out and correctly worked on the sample data you provided. Since I obviously mis-copied my Post #11 when I left out the "End Sub". I will re-post again just in case something else was screwy with the post.

Sub Fill()

    Dim lRow As Long, nSrow As Long, vlRow As Long, r As Long, lst As Boolean
   
    Application.ScreenUpdating = False
    r = 2
    vlRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    lRow = ActiveSheet.Range("F" & r).End(xlDown).Row - 1
    Range("F" & r).AutoFill Range("F2:F" & lRow), Type:=xlFillDefault
    Range("G" & r).AutoFill Range("G2:G" & lRow), Type:=xlFillDefault
    Do Until lst = True
        nSrow = lRow + 1
        lRow = ActiveSheet.Range("F" & lRow + 1).End(xlDown).Row - 1
        r = lRow
        If lRow > vlRow Then
            lRow = vlRow
            lst = True
        End If
        Range("F" & nSrow).AutoFill Range("F" & nSrow & ":F" & lRow), Type:=xlFillDefault
        Range("G" & nSrow).AutoFill Range("G" & nSrow & ":G" & lRow), Type:=xlFillDefault
    Loop
    Application.ScreenUpdating = True
   
End Sub

I would echo @Michael M sentiments on perhaps there is a disconnect somewhere. I know you were having issues with XL2BB, perhaps you may want to re-post your sample data again...

Also, I had tested @Michael M code and it did work for me.

 

[Sunline]

Yes your both right , humble apologies , tested on a 60,000 row sheet and all good . Noticed I'm only aloud to mark one post as solution when there were two , that's not fair .
Thank you both , solved .