Define a Dynamic Range in VBA

Question

Hi,

The solution to my question feels like it ought to be quite simple, but I can't work it out.

Starting from a fixed point on a sheet (cell B3)  I want to initialise an array that has a dynamic number of rows and columns. I have code that counts the number of rows and columns I need to use, but can't figure out how to tell the array how many columns to use. What I have is this (the bit I'm struggling with in bold):

Option Explicit

Public iConCtr As Integer

Sub InitArrays()

Dim lIssCtr, lRiskCtr, lControlCtr As Long

Dim rIss, rRisk, rControl  As Range

Dim wsCon As Worksheet

Dim arrBLMI() As Variant

Set wsCon = Sheets("key controls & metrics")

lIssCtr = Sheets("issues").Range("a2").CurrentRegion.Rows.Count

Set rIss = Sheets("issues").Range("a2:v" & lIssCtr)                  'this will be used later

lRiskCtr = Sheets("risks").Range("a3").CurrentRegion.Rows.Count

Set rRisk = Sheets("risks").Range("a3:ad" & lRiskCtr)             'this will be used later

ControlCount

lControlCtr = wsCon.Range("a3").CurrentRegion.Rows.Count

                          ' This is where I need to set rControl to the range "B3: " & column number calculated by iConCtr*3 & row number returned by lControlCtr

'do more stuff here

End sub

Sub ControlCount()

'count the maximum number of controls against any risk/issue

Dim rCurrentcell As Range

Dim iCtr As Integer

Set rCurrentcell = Sheets("key controls & metrics").Range("b1")

iCtr = 1

Do Until rCurrentcell.Value = ""

    Set rCurrentcell = rCurrentcell.Offset(0, 1)

    iCtr = iCtr + 1

Loop

'as there are 3 columns used per control

iConCtr = iCtr / 3

End Sub

TIA

Dave

Answer 1

If iConCtr represents the total number of columns in the range you want to define, and iConCtr*3 represents the total number of rows, and the first cell in the range is "B3", then:

  Set rControl = ActiveSheet.Range(ActiveSheet.Cells(3, 2), ActiveSheet.Cells(2 + iConCtr * 3, 1 + iConCtr))

should work.

If iConCtr represents the actual column number (not the number of columns) and iConCtr*3 represents the actual row number, then use:

    Set rControl = ActiveSheet.Range(ActiveSheet.Cells(3, 2), ActiveSheet.Cells(iConCtr * 3, iConCtr))

HTH,

Eric

Answer 2

Eric

Thanks for the quick response - knew it couldn't be too complicated!