Macro multiple range select

[ApolloID]

Hi, i need to select two ranges but not like this:

Range("E4:P4,E5:P5").select

I need to split this in two...

Can this be done?

Thanks!

 

[dk]

Hi

Can you elaborate on what you need? What do you mean by "need to split this in two"? Are you saying that you want to achieve the selection using two lines of VBA?

DK

 

[Sektor]

[COLOR="Blue"]Sub[/COLOR] SplitCells()
    
    [COLOR="Blue"]Dim[/COLOR] theAreas [COLOR="Blue"]As[/COLOR] Areas
    [COLOR="Blue"]Dim[/COLOR] rng1 [COLOR="Blue"]As[/COLOR] Range, rng2 [COLOR="Blue"]As[/COLOR] Range

    [COLOR="Blue"]Set[/COLOR] theAreas = Range("E4:P4,E5:P5").Areas
    
    [COLOR="Blue"]Set[/COLOR] rng1 = theAreas(1)
    [COLOR="Blue"]Set[/COLOR] rng2 = theAreas(2)
    
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[ApolloID]

Hi, i need to select two ranges for a chart, but the second range to offset with three rows, every time macro is looping.

Sub Loop2()
    Do
    Dim myrange As Range
Set myrange = Range("E4:P4,E5:P5")
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=myrange
    ActiveChart.ChartType = xlLineMarkers
    Loop Until IsEmpty(ActiveCell.Offset(2, 4))
End Sub

But i need this code to be something like this:

Sub Loop3()
    Do
    Dim myrange1 As Range
       Set myrange1 = Range("E4:P4")       
    Dim myrange2 As Range
       Set myrange2 = Range("E5:P5").offset(3, 0)
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:= myrange1,myrange2
    ActiveChart.ChartType = xlLineMarkers
    Loop Until IsEmpty(ActiveCell.Offset(2, 4))
End Sub

One problem is how to put together this part:

ActiveChart.SetSourceData Source:= myrange1,myrange2

...hopping the rest of the code will work (including the offset part)

Thanks alot!

 

[ApolloID]

Or at least how to add offset to the second part of the range:
ActiveChart.SetSourceData Source:=Range("E4:K4,E5:K5")
to :

ActiveChart.SetSourceData Source:=Range("E4:K4,E5:K5.offset(3,0)")

Can this be done?

 

[Sektor]

Your loop doesn't advance anywhere.
Loop Until IsEmpty(ActiveCell.Offset(2, 4)) always uses same range .

 

[ApolloID]

It worked in another situation.
maybe not here,...i did not test this macro because of the problem above.

 

[ApolloID]

Please,...any suggestion?