0

Essential, the goal is to update a graph by shifting the range by 1 with a button click.

i.e. Worksheets("Sheet1").Range("B2:B5, FB2:OR5") to

Worksheets("Sheet1").Range("B2:B5, FC2:OS5")

next click would change the range to .Range("B2:B5, FD2:OT5")...

Going with @BigBen's idea

    Dim Range1 as Range 
    Last_col = Worksheets("Sheet1").Range("ZZ216").End(xlToLeft).Column 
    First_col = Last_col - 251 
    Set Range1 = Worksheets("Sheet1").Range(Worksheets("Sheet1").Cells(216, First_col), _
        Worksheets("Sheet1").Cells(219, Last_col))

Now that I have this, how do I concatenate it back into this format .Range("B2:B5, FC2:OS5")?

Joe Chan
  • 133
  • 3
  • 11
  • This is probably an X-Y problem... probably a better way to do it than "shift a range over." Maybe determine the last column and resize to include the last 251 columns? – BigBen Apr 30 '20 at 20:23
  • Good idea, I am trying this Dim Range1 as Range Last_col = Worksheets("Sheet1").Range("ZZ216").End(xlToLeft).Column First_col = Last_col - 251 Set Range1 = Worksheets("Sheet1").Range(Cells(216, First_col), Cells(219, Last_col)) Why is the last line giving me an error? – Joe Chan Apr 30 '20 at 20:44
  • What is the value of `First_col` when the error is thrown? It might not be what you think it is. Have you looked at the value while in debug mode? – John Coleman Apr 30 '20 at 20:52
  • 2
    `Range(Cells, Cells)` is causing issues - see [this](https://stackoverflow.com/questions/8047943/excel-vba-getting-range-from-an-inactive-sheet). – BigBen Apr 30 '20 at 20:54
  • Problem was exact what BigBen pointed out – Joe Chan Apr 30 '20 at 21:44

1 Answers1

1

Using @BigBen's approach

 Dim Range0 as Range
 Dim Range1 as Range 
 Dim Range2 as Range     

 Set Range0 = Worksheets("Sheet1").Range("B2:B5")

 Last_col = Worksheets("Sheet1").Range("ZZ216").End(xlToLeft).Column 
 First_col = Last_col - 250 
 Set Range1 = Worksheets("Sheet1").Range(Worksheets("Sheet1").Cells(216, First_col), _
    Worksheets("Sheet1").Cells(219, Last_col)) 

 Set Range2 = Union(Range0, Range1)

 Worksheets("Sheet 2").ChartObjects("Chart 1").Activate
 ActiveChart.SetSourceData Source:=Range2
Joe Chan
  • 133
  • 3
  • 11