0

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?

Improve this question
3
  • No, you could use named ranges or use an array of ranges. Commented Aug 14, 2019 at 14:07
  • not sure if this is intentional, but rng2a is referring to 2 different columns D and E Commented Aug 14, 2019 at 14:29
  • @Slai Yeah, that's intentional, the second range is the data within two columns. Thanks for noticing and pointing it out just in case :) Commented Aug 14, 2019 at 14:44

4 Answers 4

Reset to default
3

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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

THANK YOU! Named ranges worked. I've never heard of the Name property for ranges, but it works perfectly. Arrays would probably work, looking at your answer and comparing to what I need, but I don't have the experience using them to have the confidence to start playing around with them yet - maybe one day :)
1

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.

Improve this answer

3 Comments

I don't have the confidence and experience yet to even play around with arrays - involves FAR too many new variables and properties compared to what I've got in my toolbox so far. I always have my variables declared properly and reference workbooks and worksheets in my code, but is it something that would be helpful within questions and answers on here??
@Emily as for what I've read on the comments, if source and destination are on different workbooks, the need to reference them is even higher my code is copying from the same sheet and workbook, so are all the codes provided by us because your code seems to be like that.
Thanks for the concern, but I'm able to understand enough about code to translate references to the same sheet and edit them to suit. I'll change this if necessary in the future, but I didn't think it was necessary in this situation as it isn't even slightly related to the problem in question - the problem is converting a string and range so that they are interchangeable in a sense (which has now been solved thanks to the property of named ranges), not referencing a range in another workbook or worksheet and so I fail to see why it would be necessary to include in my example code.
0

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
Improve this answer

1 Comment

The ranges I wanted to copy are actually in another workbook and are columns B copied to A, column D copied to B etc (all over the place basically, hence the ranges set to variables), I just edited the code so that it was much shorter and hopefully someone could change the line of code to what I wanted rather than reflecting what I actually wanted the overall outcome to be.Thanks anyway
0

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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.