Dynamic Columns Range vs Cells in VBA

Hi Frank,

Not sure if I am understanding your problem, but you seen to be complicating things a bit.

You can just name the range in excel either through the Name Manager or the little box at the top left of the screen (next to the cell reference). Once you do that, you can refer to the named range in your macro.

You can also make the range flexible (dynamic) so that it copes with increased rows and/or columns. I suggest you search dynamic range names in excel in your browser and you will find hundreds of suggestions how to go about this.

Regards,

David

Hi, let me try to restate, and thanks for your time so far David!

I have a function that runs operations on a range of cells (it colors cells depending on cell values).

Function colorCells(myRange As Range)
colors cells if ... etc . etc.

What I want to know how to do is derive a range to pass to this function from 4 different named ranges column and row values that I query from VBA.
Ultimately, I'm interested in colorCells() putting that range of cells into an array for faster processing (maybe 1000 rows to act on), I don't know if that changes whether my final range references is Range() or Cells(), but that may be another question.

So if from my workbook, VBA can return row and column information from these named ranges:

namedRange1=A or 1 for column
namedRange2=3 for row
namedRange3=C or 3 for column
namedRange4=5 for row

What is the required syntax and best method, using Range(), Cells(), or other, to construct a range variable A3:C5 (or equivalent in Cells()), that can be passed to Function colorCells(myRange As Range)? Or would I create an array and pass that to the function instead?

Part of my confusion may be that I only know how to use Cells() to refer to a single cell, not the syntax to refer to more than 1 cell, I think.

I hope this clarifies my previous post, thanks so much in advance !

I think I agree with David... I don't fully understand what your ultimate goal is but it does sound like you are complicating things somewhat. Anyway, to answer your direct question, you would create the range of cells you want to pass into your function using the named ranges for full columns and full rows by Union'ing the columns and intersecting them with the Union'ing of the rows, like this...

Intersect(Union([namedRange1], [namedRange3]), Union([namedRange2], [namedRange4]))

Here I have used the square bracket shortcut notation, here it is without using it...

Intersect(Union(Range("namedRange1"), Range("namedRange3")), Union(Range("namedRange2"), Range("namedRange4")))

Remember, this is a non-contiguous range, so your best way to iterate it is cell by cell using a For Each loop.

Hi there,

Confusing-er and confusing-er! I think this is one of those situations where a simple workbook attachment would have helped a lot!

Take a look at the attached workbook and see if it gets you moving in the right direction. It uses the following code:

Option Explicit


Sub RetrieveRange()

    Const sSHEET_NAME   As String = "Sheet1"
    Const sRANGE_1      As String = "ptrNamedRange_1"
    Const sRANGE_2      As String = "ptrNamedRange_2"
    Const sRANGE_3      As String = "ptrNamedRange_3"
    Const sRANGE_4      As String = "ptrNamedRange_4"

    Dim rDefinedRange   As Range
    Dim iColumn_Start   As Integer
    Dim iColumn_End     As Integer
    Dim iRow_Start      As Integer
    Dim iRow_End        As Integer
    Dim wks             As Worksheet

    Set wks = ThisWorkbook.Worksheets(sSHEET_NAME)

    With wks

        iColumn_Start = .Range(sRANGE_1).Column
        iColumn_End = .Range(sRANGE_3).Column
        iRow_Start = .Range(sRANGE_2).Row
        iRow_End = .Range(sRANGE_4).Row

        Set rDefinedRange = Range(.Cells(iRow_Start, iColumn_Start), _
                                  .Cells(iRow_End, iColumn_End))

    End With

    rDefinedRange.Select
    MsgBox rDefinedRange.Address

End Sub

The highlighted values may be altered to suit your requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M