Changing static to dynamic reference in a rows.Count function?

[d0rian]

Simple Q but couldn't figure this out; current code includes:

    LastRow = Cells(rows.Count, "CA").End(xlUp).row
    Arr = Split(Application.Trim(Join(Application.Transpose(Range("CA1:CA" & LastRow)))))

Except I'm constantly adding/removing columns so the static reference to column CA is no good and I want to use a dynamic reference. I named column CA "dyn_range_grab", and tried all manner of replacing the "CA" references in the code above with variations of that name, but suspect I'm getting something easy wrong. Help?

 

[Tim_Excel_]

Why not dim the columns to two variables (e.g. FirstCol & LastCol) each time you add or remove columns?
Unless you're not adding/deleting these columns in the code but instead manually outside of the code. That would require some kind of user input to let the program know what columns to look at.

 

[steve the fish]

You can find the column number then use what you already have:

myCol = Range("dyn_range_grab").Column
LastRow = Cells(Rows.Count, myCol).End(xlUp).Row
Arr = Split(Application.Trim(Join(Application.Transpose(Range(Cells(1, myCol), Cells(LastRow, myCol))))))