How to find the last columns and autofill

  • Hi helpers


    I had found the way to find the last row and autofill But the problem I don't have the vba code to find the last columns and autofill.
    In my attach xls I had a template and vba that start by entering the countifs formular in B2 (sheet1) and autofill down to the last row B15. What I need is how then to add the code in this macro to auto select B2:B15 and then autofill to the last columnL of date?? your reply is very much appreciated.


    Code
    Sub test()
    
    
        Sheets("Sheet1").Select
        Range("B2").Select
        ActiveCell.FormulaR1C1 = "=COUNTIFS(Sheet2!C1,Sheet1!R1C,Sheet2!C2,Sheet1!RC1)"
        lastrow = Cells(Rows.Count, "A").End(xlUp).Row
        Range("B2").AutoFill Destination:=Range("B2:B" & lastrow)
    End Sub
  • Re: How to find the last columns and autofill


    Will column L always be the last column? If so, adding this extra line to the end should work:

    Code
    Range("B2:B" & lastrow).AutoFill Destination:=Range("B2:L" & lastrow)
  • Re: How to find the last columns and autofill


    You may try something like this...


    Code
    Sub test()
    Dim LastRow As Long, LastCol As Long
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Range("B2:B" & LastRow).FormulaR1C1 = "=COUNTIFS(Sheet2!C1,Sheet1!R1C,Sheet2!C2,Sheet1!RC1)"
    Range("B2:B" & LastRow).AutoFill Destination:=Range("B2", Cells(LastRow, LastCol)), Type:=xlFillDefault
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!