Merging dynamic ranges into one Range Excel or VBA

Question

I am currently trying to take two ranges and combine them into one range. My ranges are dynamic because they change based off the date. For example, Suppose the two ranges I want to combine are A3:A10 and the other C7:C12. And every day it refreshes and moves the index by 1... so the new ranges are A4:A11 and C8:C13. I want to combine the two into one range into a different column. I am assuming that this will have to be implemented in vba... however, I have been having minimal luck. I have values that indicate what row number I want to make my ranges within my worksheet. I've tried making VBA macros, but I have been having no luck. I keep getting 9(the first term of the range I wanted) as my result and not a range, but I want to use the function to print the whole combined range. I have also thought about using Sub, but I am not very experienced in using Sub.

Here's what I have so far... Please let me know any suggestions or tips.

Function FiveYTwoY()
 Worksheets("India Data").Activate
 index5_90 = Cells(10, 2).Value '5Y 90 day index
 index5_yes = Cells(9, 2).Value '5Y yesterday index
 index2_90 = Cells(7, 2).Value  '2Y 90 day index
 index2_yes = Cells(6, 2).Value '2Y yesterday index
 Dim range5 As Range
 Set range5 = Range(Cells(index5_90, 20), Cells(index5_yes, 20))

 Dim range2 As Range
 Set range2 = Range(Cells(index2_90, 17), Cells(index2_yes, 17))
 FiveYTwoY = Union(range2, range5)

End Function

Thanks for the help

Do you need `Function FiveYTwoY() as Range`? Use F8 (single cycle) and `Debug.Print range5.Address`, etc. — dcromley, Jul 16 '14 at 20:47

score 0 · Answer 1 · edited May 23 '17 at 12:05

0

You should avoid the use of .Select/Activate. You may want to see THIS. Fully qualify your objects.

Also you have to use Set to assign ranges as shown below

Sub Sample()
    Dim range5 As Range, range2 As Range, FiveYTwoY As Range

    With Worksheets("India Data")
        index5_90 = .Cells(10, 2).Value '5Y 90 day index
        index5_yes = .Cells(9, 2).Value '5Y yesterday index
        index2_90 = .Cells(7, 2).Value  '2Y 90 day index
        index2_yes = .Cells(6, 2).Value '2Y yesterday index

        Set range5 = .Range(.Cells(index5_90, 20), .Cells(index5_yes, 20))
        Set range2 = .Range(.Cells(index2_90, 17), .Cells(index2_yes, 17))

        Set FiveYTwoY = Union(range2, range5)

        With FiveYTwoY
            '
            '~~> Do whatever you want with that range here
            '
        End With
    End With
End Sub

edited May 23 '17 at 12:05

Community

1
1

answered Jul 16 '14 at 20:47

Siddharth Rout

147,039
17
206
250

Thanks! I will see if this works. How would I print the range into a worksheet? – user3757405 Jul 16 '14 at 20:55
`FiveYTwoY.Copy DestRng` – Siddharth Rout Jul 16 '14 at 20:57
for some reason it is saying Sub undefined... not sure why – user3757405 Jul 16 '14 at 21:05
something wrong with my DestRng. I'll figure it out. Thanks so much you've been awesome! – user3757405 Jul 16 '14 at 21:10
`DestRng` is an example. It is the destination range where you have to paste. For example `FiveYTwoY.Copy Worksheets("India Data").Range("A40")` – Siddharth Rout Jul 16 '14 at 21:11
Sorry to bother you.... but I am getting that command cannot be used on multiple selections when I try to copy fiveYtwoY – user3757405 Jul 17 '14 at 13:06
Ah yes. My apologies. So where do you want to copy them? – Siddharth Rout Jul 17 '14 at 13:10
I want to copy the whole range and paste it into a selected cell. Is this possible? Thats why I wanted a function, but that's probably not possible right? – user3757405 Jul 17 '14 at 13:14
I ended up doing this in python, but if you figure it out please let me know. Thanks again for your help I really appreciate all the time you put in to help me. – user3757405 Jul 17 '14 at 18:18

Merging dynamic ranges into one Range Excel or VBA

1 Answers1