Excel macro - Selecting cell range with a using number from loop

Question

I am trying to define a cell range (i.e. A1:F1) using the number from my iCounter as the row number.

Presently, my code is formatted like

Range(A & (iCounter - 2):F1 & (iCounter - 1)).Select

I am getting the Syntax Error statement. I have researched and reformatted with quotation marks but I am still unable to get the format correct.

I have based this on a recording that used the hard coded:

Range("A1:F1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark2
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

The code block I have adapted is below. Thank you!

Step 5:  Start reverse looping through the range.
    For iCounter = MyRange.Rows.Count To 2 Step -3
    
'Step 6: Copy and Paste columns
        '
        Range("A" & (iCounter - 1)).Cut Range("A" & (iCounter - 2))
        'Add a color fill
        Range(A & (iCounter - 2):F1 & (iCounter - 1)).Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark2
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With

        
'Step 7: Increment the counter down
    Next iCounter

Also see [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, Jul 28 '21 at 20:28
You could avoid `Select` by using `with Range("A" & iCounter - 2 & ":F" & iCounter - 1)` — cybernetic.nomad, Jul 28 '21 at 20:29
Thank you so much. I think I was missing the " before the colon `":F"` — user3324136, Jul 28 '21 at 20:29
I assume `Select` can introduce some errors? I will read the link. Thank you @BigBen and @cybernetic.nomad. — user3324136, Jul 28 '21 at 20:30

Excel macro - Selecting cell range with a using number from loop

0 Answers0