Excel VBA: Dynamic Range Based on Cell Value (3 Methods)

This article illustrates how to define a dynamic range in Excel based on a cell value using VBA code. We’re going to explain 3 different examples to define and select a dynamic range with the necessary explanation.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


3 Examples of Defining Dynamic Range Based on Cell Value

The following section describes how to open and write code in the visual basic editor.

Write Code in Visual Basic Editor

Follow the steps to open the visual basic editor and write some code there.

  • Go to the Developer tab from the Excel Ribbon.
  • Click the Visual Basic option.

  • In the Visual Basic For Applications window, click the Insert dropdown to select the New Module option.

Now that a new module is opened, write some code there and press F5 to run.


1. Select Dynamic Range Based on the Value of Another Cell Using VBA in Excel

Let’s say we have a list that contains a bunch of city names in cells A1:A7 with their country names in the next column (cells B1:B7). We want to configure a code that will select multiple rows from these two columns. But the number of rows to be selected should not be hardcoded, rather, we want to make it dynamic based on a cell value.

Excel VBA Dynamic Range Based on Cell Value

Here in cell E3, we’re going to put the number of rows (in this example, 3) to select. To do this, copy and paste the following code into the visual code editor.

Sub DynamicRangeBasedOnCellValue()
Dim DValue As Variant
Dim DRange As Range
DValue = ActiveSheet.Range("E3").Value
Set DRange = ActiveSheet.Range("A1:B" & DValue)
DRange.Select
End Sub

Excel VBA Dynamic Range Based on Cell Value

Now press F5 to run the code.

Excel VBA Dynamic Range Based on Cell Value

As an output, we’ve successfully selected the first 3 rows of the dataset. If we put 5 in cell E3 and run the code again.

Excel VBA Dynamic Range Based on Cell Value

That’s how we can change the dynamic range to be selected by running the VBA code.

Read More: Excel Dynamic Range Based on Cell Value


2. Define Dynamic Range Based on Cell Values Using VBA in Excel

In this example, we’ll show how to define and then select a dynamic range based on two cell values i.e., one cell value to define the starting and another to ending of the dynamic range. To illustrate this, let’s count in the following dataset ranging from A1:H12. In your case, it can be multiple times larger than this one. We’ve specified two cells in the worksheet- B15 and C15, from where we’re going to take the starting and ending range data of our dynamic range in our VBA code.

To solve the above issue-

  • We need to define two cells that hold the starting and end of the dynamic range. Here we defined cells B15 and C15 to hold the starting and end of the range i.e., A1 to H5 in this example.

Excel VBA Dynamic Range Based on Cell Value

  • Then we need to copy and paste the following code into the visual code editor.
Sub SelectDynamicRange()
  Dim sRange As String, eRange As String
  sRange = ActiveSheet.Range("B15"):
  eRange = ActiveSheet.Range("C15")
  Range(sRange & ":" & eRange).Select
End Sub

In this code, we declared two different variables named sRange and eRange to hold the starting and end cell reference of the dynamic range in cells B15 and C15 respectively.

  • Press F5 to run the code. We’ve successfully selected the range of Rangel A1:H5.

Excel VBA Dynamic Range Based on Cell Value

If we change the values of cells B15 and C15 to some other value, the selected range will change accordingly.

Excel VBA Dynamic Range Based on Cell Value

Read More: Create Dynamic Sum Range Based on Cell Value in Excel (4 Ways)


Similar Readings


3. Copy and Paste Dynamic Range Based on Cell Value with Excel VBA

In this illustration, we have a demo sales info template that one shop manager has to create every day to store sales information. Using VBA code, we’re going to copy and paste the template to a new worksheet based on a cell value that holds the worksheet name. In addition, we’ll be able to decide what portion of the template should be copied and then pasted to our desired worksheet. Here is the template in the below screenshot which is in the “Source” worksheet.

To facilitate the user to choose what columns to copy in the new worksheet, we inserted the column names in cells G2:G6. And in cell H2, we put the source worksheet name, form where we’re going to copy the template to a new worksheet. We need to put the new worksheet name in cell I2.

Excel VBA Dynamic Range Based on Cell Value

Let’s say we’ve created a new worksheet for Day 1 of a month named “Day1”. In cell I2, we put the new worksheet name. In addition, in cells G2:G6 we put the column names to copy in the worksheet named Day1.

Now copy and paste the following code in the visual code editor.

Sub CopyPasteDynamicRange()
    Dim CSheet As Worksheet, PSheet As Worksheet
    Dim CRange As Range, PRange As Range
    Dim CLastRow As Long, CLastCol As Long, PLastCol
    Dim ColArray As Variant
    Dim ColName As Variant
    Dim ColNo As Long
    Dim a, b As String
    a = ActiveSheet.Range("H2").Value
    b = ActiveSheet.Range("I2").Value
    Set CSheet = Sheets(a)
    Set PSheet = Sheets(b)
    CLastRow = CSheet.Range("A" & Rows.Count).End(xlUp).Row          ' last row
    CLastCol = CSheet.Cells(1, Columns.Count).End(xlToLeft).Column   ' last column
    With CSheet
        Set CRange = .Range(.Cells(1, "A"), .Cells(CLastRow, CLastCol))
    End With
    PLastCol = PSheet.Cells(1, Columns.Count).End(xlToLeft).Column   ' last column
    ColArray = Range("G2:G6").Value ' column headers
    For Each ColName In ColArray
        ColNo = Application.Match(ColName, CRange.Rows(1), 0)
        PLastCol = PLastCol + 1
        CRange.Columns(ColNo).Copy Destination:=PSheet.Cells(1, PLastCol)
    Next ColName
End Sub

Press F5 to run the code. Now navigate to the worksheet named “Day1”. The output is here below.

Excel VBA Dynamic Range Based on Cell Value

Similarly, we can make another worksheet for Day2 and run the code again after changing the I2 cell value to Day2.

Excel VBA Dynamic Range Based on Cell Value

The output is-

Excel VBA Dynamic Range Based on Cell Value

Now let’s say we need to copy the template without the Tax(%) column in the Day3 worksheet. For this, we need to set up the values like this.

In the output, in the Day3 worksheet, we have the template but without the Tax column.

Excel VBA Dynamic Range Based on Cell Value

Read More: How to Use Dynamic Range for Last Row with VBA in Excel (3 Methods)


Things to Remember


Conclusion

Now, we know how to define a dynamic range in Excel based on a cell value using VBA code. Hopefully, it would help you to use these methods more confidently. Any questions or suggestions don’t forget to put them in the comment box below.


Related Articles

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo