Dynamic Columns Range vs Cells in VBA

[Frank Nunez]

Excel 2019

Hi all, wondering if anyone has any preferences or recommendations on dynamic column and row references in Excel VBA? Each time I move a column, my rigid Range references ie "A1" break.
This is a real basic, structural thing that I want to solve with the least computation while I re-write a whole bunch of macros in my workbook that I've been building for 5 years.

Some recommended putting headers at the top of columns and looping through a search in VBA for the headers to return a column number.

I thought if I made each column header a named range, it would be faster for Excel to get the column number by Range("namedRange1").Column. And the same for Row.
That works fine. Please comment if that's not an ideal method computationally.

My new challenge is what syntax to use in combining Range and Cells, if for instance I want to construct a new Range across several cells derived from several different named range's returned column and row numbers.

So if,
namedRange1 column = A
namedRange2 row = 5
namedRange3 column = C
namedRange4 row = 10

desiredRange = Range(Cells(Range("namedRange1").column,Range("namedRange2".row)),Cells("namedRange3").column,Range("namedRange4".row))

So I can wind up with something akin to
desiredRange = Range("a5:c10")

Or is there a way to construct a kind of compound Cells() reference which would be more ideal?

Somehow I'm having a hard time finding the right reference materials online to help me understand this, and I know it's must be very basic, thanks in advance!

Best, Frank

[David A Coop]

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

[Frank Nunez]

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 !

[Rick Rothstein]

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.

[Greg M]

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:

Please Login or Register  to view this content.

The highlighted values may be altered to suit your requirements.


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

Regards,

Greg M

[FlameRetired]

Administrative Note:

Hello Frank. Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until the link is provided.)

Since you are new here as a courtesy I am posting the link for you this time. https: //www.mrexcel.com/board/threa...alues.1142468/