Unknown Range Code Assistance

[mt03530]

Hello!

For whatever reason I am having difficulty wrapping my head around how to write code to encompass a dynamic range. I've done some online searches but those codes never seemed to work.

A piece of code I am working on now came from Record Macro, but I need the Auto Fill to encompass B2 through an unknown column length instead of B2 through B26.

Using my example below, could someone please explain how to write this code process?

Range("B2").Select        
        ActiveCell.FormulaR1C1 = _
            "=IFERROR(INDEX('Query Results'!C[7],MATCH(Summary!RC[-1],'Query Results'!C[3],0)),"""")"
        Range("B2").Select
        Selection.AutoFill Destination:=Range("B2:B26")
        Range("B2:B26").Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
           :=False, Transpose:=False

Thank you very much!!

 

[Scott T]

Is this what you want?

Sub fdsfs()
Dim lr As Long
lr = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Range("B2").FormulaR1C1 = _
            "=IFERROR(INDEX('Query Results'!C[7],MATCH(Summary!RC[-1],'Query Results'!C[3],0)),"""")"
        Range("B2").AutoFill Destination:=Range("B2:B" & lr)
        Range("B2:B" & lr).Copy
        Range("B2:B" & lr).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
           :=False, Transpose:=False
        Application.CutCopyMode = False
End Sub

 

[mt03530]

Is this what you want?

Sub fdsfs()
Dim lr As Long
lr = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Range("B2").FormulaR1C1 = _
            "=IFERROR(INDEX('Query Results'!C[7],MATCH(Summary!RC[-1],'Query Results'!C[3],0)),"""")"
        Range("B2").AutoFill Destination:=Range("B2:B" & lr)
        Range("B2:B" & lr).Copy
        Range("B2:B" & lr).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
           :=False, Transpose:=False
        Application.CutCopyMode = False
End Sub

Yes! That is exactly what I was wanting. Would you mind walking me through what each piece means?

 

[Scott T]

This declares a variable lr then sets lr to the last row by using the data in column A. Using rows.count gets the total number of rows in the sheet. So the code goes to the last cell in A and then end(xlUP) is like using ctrl +up arrow to jump to the last cell with data in it. This gives the last row where you have data

Dim lr As Long
lr = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row

This is the original code without the selects as they are not need and the hard coded range is change to use the lr variable .
If lr is = to 7 then Range("A" & lr) is the same as saying Range("A7")

Range("B2").FormulaR1C1 = _
            "=IFERROR(INDEX('Query Results'!C[7],MATCH(Summary!RC[-1],'Query Results'!C[3],0)),"""")"
        Range("B2").AutoFill Destination:=Range("B2:B" & lr)
        Range("B2:B" & lr).Copy
        Range("B2:B" & lr).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
           :=False, Transpose:=False

This line stops the marching ants from staying on screen

Application.CutCopyMode = False

 

[mt03530]

This declares a variable lr then sets lr to the last row by using the data in column A. Using rows.count gets the total number of rows in the sheet. So the code goes to the last cell in A and then end(xlUP) is like using ctrl +up arrow to jump to the last cell with data in it. This gives the last row where you have data

Dim lr As Long
lr = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row

This is the original code without the selects as they are not need and the hard coded range is change to use the lr variable .
If lr is = to 7 then Range("A" & lr) is the same as saying Range("A7")

Range("B2").FormulaR1C1 = _
            "=IFERROR(INDEX('Query Results'!C[7],MATCH(Summary!RC[-1],'Query Results'!C[3],0)),"""")"
        Range("B2").AutoFill Destination:=Range("B2:B" & lr)
        Range("B2:B" & lr).Copy
        Range("B2:B" & lr).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
           :=False, Transpose:=False

This line stops the marching ants from staying on screen

Application.CutCopyMode = False

That makes 100% sense! Thank you so so much for the explanation!!!