Results 1 to 6 of 6

Dynamic Columns Range vs Cells in VBA

Threaded View

  1. #1
    Registered User
    Join Date
    08-08-2020
    Location
    Boston
    MS-Off Ver
    2019
    Posts
    33

    Dynamic Columns Range vs Cells in VBA

    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
    Last edited by Frank Nunez; 08-08-2020 at 05:46 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Possibility to use dynamic range while outputting range to specific columns
    By jaryszek in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-02-2019, 09:40 AM
  2. Dynamic Range: For Each C in R : what if R has 2 columns?
    By superlative in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 04-21-2019, 02:40 PM
  3. [SOLVED] How to create a dynamic range if the last cell is empty and the columns have empty cells
    By peter_swe in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-03-2016, 11:18 PM
  4. Replies: 0
    Last Post: 08-03-2016, 07:01 PM
  5. Dynamic range for columns
    By amartino44 in forum Excel General
    Replies: 6
    Last Post: 08-29-2013, 06:52 PM
  6. [SOLVED] Dynamic Table Range, Dynamic Columns
    By bimo in forum Excel General
    Replies: 6
    Last Post: 06-24-2013, 08:16 AM
  7. Dynamic Range with 2 columns
    By Pasha81 in forum Excel General
    Replies: 2
    Last Post: 11-11-2009, 08:00 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1