Selecting non sequential columns including dynamic row number

Question

I need to create a macro that selects two non-sequential columns that have a dynamic row reference.

I.e. The static macro would be like this:

Range("A7:A54,C7:C54").Select

My issue is that the last row is dynamic, so I have added a variable called Height that calculates this (not important how I get this row number, so will not show that code here).

The macro I have tried to implement is therefore:

Range("A7:A" & Height, "C7:C" & Height).Select

Height being 54 in this case.

But when I run the macro it select the column in between as well! I.e. also column B (A7:C54)

Can anyone help me figure out how I can adjust the code to not select the column in between?

Though one should normally [avoid Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, Mar 15 '21 at 14:41

Pᴇʜ · Accepted Answer · 2021-03-15T14:55:51.140

2

Either as BigBen commented

Range("A7:A" & Height & ",C7:C" & Height).Select

but address strings are limited to 255 characters, so if you run into that limit you would need to use union() with multiple range objects:

Union(Range("A7:A" & Height), Range("C7:C" & Height)).Select

edited Mar 15 '21 at 14:55

answered Mar 15 '21 at 14:46

Pᴇʜ

56,719
10
49
73

Selecting non sequential columns including dynamic row number

1 Answers1