Any way of selecting column letter from a dynamic range?

[Magnatolia]

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!

 

[JoeMo]

Maybe something like this where col H is a named range "myCol":

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

 

[mikerickson]

Perhaps

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

 

[Magnatolia]

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!

 

[Rick Rothstein]

Assuming MyCol is a Defined Name for the column of interest...

MsgBox Range("A1", Cells(Rows.Count, "A").End(xlUp)).Offset(, Range("MyCol").Column).Address

 

[JoeMo]

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.

 

[Rick Rothstein]

Assuming MyCol is a Defined Name for the column of interest...

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....

MsgBox Range("A[COLOR=#FF0000][B]2[/B][/COLOR]", Cells(Rows.Count, "A").End(xlUp)).Offset(, Range("MyCol").Column).Address