Any way of selecting column letter from a dynamic range?

Magnatolia

Board Regular
Joined
Jan 19, 2012
Messages
81
Office Version
  1. 365
Platform
  1. Windows
Hi guys,

I'm doing some work on a really complex macro-enabled group of workbooks and the client wants to insert a couple of columns. The original creator of the macro has used static selections so I can get around columns by making each column a dynamic named range. However in some parts they specify for example H1 to the last row of data on A. Is there any way of having something like Range(rangename.column & "1" to rangename.column & "1000") so that way if the range in H is shifted one to the right the range selection will still work?

These spreadsheets are 30mb from all the macros and don't really want to keep manually hunting for all those small static selections every time a change needs to occur to the layout.

Thanks!
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Maybe something like this where col H is a named range "myCol":
Code:
Sub DynamicRange()
'Col H is a named range with name "myCol"
'This selects the portion of col H from H1 to the last filled row in col A
lr = Range("A" & Rows.Count).End(xlUp).Row
Range(Range("myCol")(1), Cells(lr, Range("myCol").Column)).Select
End Sub
 
Upvote 0
Perhaps
Code:
With Range("A:A")
    With Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlup)).EntireRow
        MsgBox Application.Intersect(myColumn.EntireColumn, .Cells).Address
    End With
End With
 
Upvote 0
JoeMo, that's awesome! What does the (1) do exactly?

Also, what if I wanted to move the selection down one row to skip the column headings? For example in the example you used, if I wanted to select from H2 to the last row on A?

Thanks!
 
Upvote 0
Assuming MyCol is a Defined Name for the column of interest...

Code:
MsgBox Range("A1", Cells(Rows.Count, "A").End(xlUp)).Offset(, Range("MyCol").Column).Address
 
Upvote 0
JoeMo, that's awesome! What does the (1) do exactly?

Also, what if I wanted to move the selection down one row to skip the column headings? For example in the example you used, if I wanted to select from H2 to the last row on A?

Thanks!
The (1) is the first cell in the named range,H1 in this example. Change it to (2) to start the selection at H2.
 
Upvote 0
Assuming MyCol is a Defined Name for the column of interest...

Code:
MsgBox Range("A[COLOR=#FF0000][B]1[/B][/COLOR]", Cells(Rows.Count, "A").End(xlUp)).Offset(, Range("MyCol").Column).Address
Also, what if I wanted to move the selection down one row to skip the column headings?
For my code line, just change the highlighted 1 to a 2....
Code:
MsgBox Range("A[COLOR=#FF0000][B]2[/B][/COLOR]", Cells(Rows.Count, "A").End(xlUp)).Offset(, Range("MyCol").Column).Address
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top