application defined error when naming a variable

Question

I am trying to define multiple ranges within excel and am ending with 'Application defined or object defined error'

the code is

Dim r As Range
Dim r1 As Range
Dim r2 As Range
Dim r3 As Range
Dim r4 As Range
Dim r5 As Range
Dim r6 As Range
Dim r7 As Range
Dim r8 As Range
Dim multiplerange As Range


Set SCH22 = Workbooks.Open("G:\CONTRFRM 22-23.xlsm")


Set r = SCH22.Worksheets("CONTFRM22-23").Range("A" & i, "B" & i)
Set r1 = SCH22.Worksheets("CONTFRM22-23").Range("C" & i, "D" & i)
Set r2 = SCH22.Worksheets("CONTFRM22-23").Range("E" & i, "F" & i)
Set r3 = SCH22.Worksheets("CONTFRM22-23").Range("H" & i, "J" & i)
Set r4 = SCH22.Worksheets("CONTFRM22-23").Range("K" & i, "L" & i)
Set r5 = SCH22.Worksheets("CONTFRM22-23").Range("M" & i, "P" & i)
Set r6 = SCH22.Worksheets("CONTFRM22-23").Range("Q" & i, "R" & i)
Set r7 = SCH22.Worksheets("CONTFRM22-23").Range("S" & i, "Y" & i)
Set r8 = SCH22.Worksheets("CONTFRM22-23").Range("AD" & i)

Set multiplerange = Union(r1, r2, r3, r4, r5, r6, r7, r8)

The error is on the set r = line and i cannot figure out why.

If anyone has a better way of defining a range that is more than 2 arguments long that also would be great!

Thanks

@cybernetic.nomad: `.Range("A1:B1") = .Range("A1", "B1")` i.e. `.Range("A" & i & ":B" & i) = .Range("A" & i, "B" & i) = .Range("A:B").Rows(i) = .Columns("A:B").Rows(i)`. — VBasic2008, Oct 25 '22 at 19:41
You could use something like `Set multiplerange = Intersect(ws.Range("A:F,H:Y,AD:AD"), ws.Rows(i))` instead of all those variables. I'm not sure what the `i` variable represents though. Share more detail or more code to get an accurate suggestion. — VBasic2008, Oct 25 '22 at 19:48

score 1 · Answer 1 · answered Oct 25 '22 at 20:07

Loop Through Rows of Discontinuous Columns

Note that instead of the i variable, I'm using the r variable to loop through Rows.

Option Explicit

Sub LoopThroughRows()
    
    Dim wb As Workbook: Set wb = Workbooks.Open("G:\CONTRFRM 22-23.xlsm")
    Dim ws As Worksheet: Set ws = wb.Worksheets("CONTFRM22-23")
    Dim crg As Range: Set crg = ws.Range("A:F,H:Y,AD:AD")
    
    Dim rrg As Range
    Dim r As Long
    
    For r = 1 To 10 ' adjust!
        Set rrg = Intersect(crg, ws.Rows(r))
        ' e.g.:
        Debug.Print rrg.Address
    Next r

End Sub

Thank you for that. I have changed the way I was doing it now so I no longer need the code but it might be useful in the future! — Luke, Oct 27 '22 at 07:14

application defined error when naming a variable

1 Answers1

Loop Through Rows of Discontinuous Columns