Is it possible to make the start and ending cells of a range a variable?

user_18332078 0
Viewed 47

I'm trying to write a code that makes a graph from different parts of the data I have, the issue is that I need a for loop to go through all my lines of data and when the row changes also the range for the next graph

Basically I need the code to check when i is 1 to graph using the range A2:A12 from a different sheet (The table below exists on Sheet1, while the range is from Sheet2)

Cycle Starting range Ending range
1 A2 A12
2 A22 A32

The issue I'm facing is that when I try to define Range("Variable 1:Variable 2") the code doesn't seem to work.

I'm sure I'm thinking about this wrong but I haven't found any solutions online.

vba excel
2 Answers

Never use Range or Cells (as well as Columns, Rows) without specifying in which sheet you expect them to be. Otherwise your code depends on which workbook/worksheet is active the moment it runs and your code is not reliable.

Use something like

ThisWorkbook.Worksheets("Sheet2").Range(…)

or better define a variable so you don't have to repeate it:

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet2")

'and then always use
ws.Range(…)

To use variable ranges you either need to concatenate your variables to a valid address you can use:

ws.Range(Variable1 & ":" & Variable2)

or simly use

ws.Range(Variable1, Variable2)

where for example Variable1 = "A2" and Variable2 = "A12"

user_32196130

Reference Ranges Using a List of Cell Addresses

Option Explicit

Sub ReferenceRanges()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim sws As Worksheet: Set sws = wb.Worksheets("Sheet1")
    Dim dws As Worksheet: Set dws = wb.Worksheets("Sheet2")
    
    Dim drg As Range
    Dim r As Long
    
    For r = 2 To sws.Cells(sws.Rows.Count, "B").End(xlUp).Row

        ' Note the importance of using '.Value' (try without it).
        Set drg = dws.Range(sws.Cells(r, "B").Value, sws.Cells(r, "C").Value)
        
        ' Continue using 'drg', e.g.:
        
        Debug.Print drg.Address(0, 0)
    
    Next r
    
End Sub
user_98140690
Related