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