CODEWITHSUNDEEP
vbaexcel
D.Martin
D.Martin

Reputation: 15

Create Range excluding column in middle

I have the problem of needing to exclude a column in the middle of my excel worksheet. Is there a way of creating a range that excludes a column not on the edges of the data. The range is dynamic ranging from A1:AA#. The column "F" needs to be excluded from this range. This range needs to be stored for use in a pivotTable.

Range("A1:E4,G1:J4").Select

This is how the excel macro recorder creates a range with a gap, but I cannot find a way to modify this so the last cell is dynamic.

Thanks.

Upvotes: 1

Views: 10694

Answers (3)

DisplayName
DisplayName

Reputation: 13386

you could use

Dim rng As Range
Set rng = Intersect(Range("A:E,G:AA"), Rows(1).Resize(Cells(Rows.Count, 1).End(xlUp).Row))

where the column index in Cells(Rows.Count, 1) lets you choose what column size your range after

Upvotes: 0

urdearboy
urdearboy

Reputation: 14580

Just as you should avoid using the .Select Method, you should also avoid using the .UsedRange when possible (See here).

You can try something like this. It is not as clean, but may prove to be less bug prone. 

Dim LRow As Long
LRow = Range("A" & Rows.Count).End(xlUp).Row

Dim MyRange1 As Range, MyRange2 As Range, BigRange As Range

Set MyRange1 = Range("A1:E" & LRow)
Set MyRange2 = Range("G1:J" & LRow)
Set BigRange = Application.Union(MyRange1, MyRange2)

BigRange.Select

You can then refer to your BigRange directly moving forward.

Upvotes: 2

J.Doe
J.Doe

Reputation: 596

If you have only one set of data in your sheet, you could try something like that :

Intersect(ActiveSheet.UsedRange, Range("A:E,G:AA")).Select

This will select everything that contains data up to column AA on the sheet except for column F.

Whenever possible , you should avoid using .select .activate but you only provide one line of your code so I can't help you much on that part except redirect you to this.

Upvotes: 1

Related Questions