CODEWITHSUNDEEP
excelvbastringvariablesrange
Emily
Emily

Reputation: 18

Is there a way to reference a range variable using strings?

I've already used Set to create ranges with names in the format rng1a. I then use a loop to go through i (integer) values, and want to set the final range to use to be the one that has the name in the form 'rng' & i & "a"

My initial thought was something along the lines of Range("rng" & i & "a"), however this results in an error.

Set rng1a = Range("B2", Range("B2").End(xlDown))
Set rng2a = Range("D2", Range("E2").End(xlDown))
i = 1
Do
    ("rng" & i & "a").Copy                      'this is the problem
    Range("A2").End(xlDown).Offset(1,0).PasteSpecial xlPasteValues
    i = i + 1
Loop Until i = 3

I keep getting an error message with

run-time error '1004':
Method 'Range' of object '_Global' failed

My thought is that I need to format the name of the range as a string so that it can be recognised as the name of a range. Is there a way to do this?

Upvotes: 0

Views: 162

Answers (4)

Slai
Slai

Reputation: 22876

You can have array of ranges :

Set rng1a = Range("B2", Range("B2").End(xlDown))
Set rng2a = Range("D2", Range("E2").End(xlDown))

For Each rng in Array(rng1a, rng2a)
    rng.Copy
    Range("A2").End(xlDown).Offset(1,0).PasteSpecial xlPasteValues
Next

Upvotes: 0

Harassed Dad
Harassed Dad

Reputation: 4704

What you actually want to do (I think) is copy the contents of the columns B,C D etc, into Column A underneath each other.

Sub CopyStuff
Dim i as integer
dim r as range
for i = 1 to 6
    set r = range(cells(1,i),cells(1,i).end(xldown))
    r.copy 
    range("a1").end(xldown).offset(1,0).pastespecial xlpastevalues
next i

End sub

Upvotes: 0

SJR
SJR

Reputation: 23081

I haven't tested either of these, but I think they should work.

Be wary of using End(xldown) as if you don't have anything underneath the first cell you will go straight to the very last cell. Better to work up from the bottom (see Damian's answer).

Sub x1()

'Array

Dim rng(1 To 2) As Range, i As Long

Set rng(1) = Range("B2", Range("B2").End(xlDown))
Set rng(2) = Range("D2", Range("E2").End(xlDown))

For i = 1 To 2
    rng(i).Copy
    Range("A2").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues
Next i

End Sub

Sub x2()

'Named ranges

Dim i As Long

Range("B2", Range("B2").End(xlDown)).Name = "rng1a"
Range("D2", Range("E2").End(xlDown)).Name = "rng2a"

For i = 1 To 2
    Range("rng" & i & "a").Copy
    Range("A2").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues
Next i

End Sub

Upvotes: 3

Damian
Damian

Reputation: 5174

This should do it:

Option Explicit
Sub Test()

    Dim i As Long, LastRow As Long
    Dim arrRanges(1 To 2) As Range

    With ThisWorkbook.Sheets("NameYourSheet") 'change the sheet name
        Set arrRanges(1) = .Range("B2", .Range("B2").End(xlDown))
        Set arrRanges(2) = .Range("D2", .Range("E2").End(xlDown))
        For i = LBound(arrRanges) To UBound(arrRanges)
            LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
            arrRanges(i).Copy .Range("A" & LastRow)
        Next i
    End With

End Sub

Remember to always declare all your variables, and reference to workbooks and worksheets.

Upvotes: 1

Related Questions