AutoFill the first emty column based on the values of the last filled column

[runneer]

Dear all,

I'm new user of Excel VBA.

The following code autofills column EX based on the values of column EW:

    Sub mfill()
          Columns("EW:EW").Select
          Selection.AutoFill destination:=Columns("EW:EX"), Type:=xlFillDefault
    End Sub

I would like to find a way to automatically find the last filled column and use this to autofill the column next to that. In the above example I would like to change EW with the last filled column and EX with the column next to that. How can I do that?

Thanks a lot!

[JOHN H. DAVIS]

Maybe:

Sub runneer()
Range(Cells(1, ActiveSheet.UsedRange.Columns.count), Cells(ActiveSheet.UsedRange.Rows.count, ActiveSheet.UsedRange.Columns.count + 1)).Interior.ColorIndex = 6
End Sub

[runneer]

Thanks a lot John for your help. I tried your Sub() but it changes the interior color of the last filled column and column next to that to yellow. It does not auto fill the values of the first empty column though. Is there anyway to do that?
Thanks!

[runneer]

I played around with your suggestion John together with the help from http://msdn.microsoft.com/en-us/libr.../ff195345.aspx and I think I solved my problem! Here is the code:

Sub mfill()

    Set SourceRange = Range(Cells(1, ActiveSheet.UsedRange.Columns.Count), Cells(ActiveSheet.UsedRange.Rows.Count, ActiveSheet.UsedRange.Columns.Count))
    Set fillRange = Range(Cells(1, ActiveSheet.UsedRange.Columns.Count), Cells(ActiveSheet.UsedRange.Rows.Count, ActiveSheet.UsedRange.Columns.Count + 1))
    SourceRange.AutoFill destination:=fillRange, Type:=xlFillDefault
End Sub

[JOHN H. DAVIS]

runneer. Glad you got a solution. I know my suggestion change the color. It was my mistake. I was testing for filling the last used column and offsetting one column. I often test that way. I lost track of filling the formula as you required.

[debbiez]

no help[