在VBA中，是否有一种简单的方法可以在每次单击按钮时将范围(“B2:B5，FB2:OR5")移到1以上？

Question

本质上，目标是通过单击按钮将范围移动1来更新图形。

即工作表(“Sheet1”).Range(“B2:B5，FB2:OR5")到

工作表(“Sheet1”).Range(“B2:B5，FC2:OS5")

下一次单击会将范围更改为.Range("B2:B5，FD2:OT5")...

遵循@BigBen的想法

    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)) 

现在我有了它，我如何将它连接回这个格式的.Range("B2:B5，FC2:OS5")？

Answer 1

使用@BigBen的方法

 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