How to find the last columns and autofill

Zeanah · Jun 1st 2017

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

Trevor_S · Jun 3rd 2017

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)

sktneer · Jun 3rd 2017

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

How to find the last columns and autofill

Participate now!

Share