0

My problem is relatively simple: I'm attempting to create a named range by taking the Active Sheet's Used Range but excluding Column A.

Essentially I'm attempting to figure out how to do the opposite of a Union.

How would I go about this?

Thank you.

I haven't tried any solutions as none of the solutions I've found on Google or here on StackExchange are applicable to my current situation. All of them are highly specific, and I just need a general answer on how to remove cells from a defined range.

braX
  • 11,506
  • 5
  • 20
  • 33
Ian
  • 1
  • 1
  • 1
    Use [THIS POST](https://stackoverflow.com/questions/11169445/find-last-used-cell-in-excel-vba) to help you find the last column and last row. Then you can use that to set your range starting at B1 and ending with the last column and last row. – Scott Craner Feb 02 '23 at 20:44
  • 2
    There is no function that would do the opposite of `Union`. When you need it, you handle it differently from case to case. In your case, to reference the used range without the first column, you could do something like `Set rg = ws.UsedRange.Resize(, ws.UsedRange.Columns.Count - 1).Offset(, 1)`. – VBasic2008 Feb 02 '23 at 21:28

1 Answers1

0

How about this:

Sub RangeMinusFirstColumn()

    Dim rng As Range
    Dim sh As Worksheet
    Set sh = ActiveSheet
    Set rng = sh.Range("A1").CurrentRegion

    Set rng = rng.Offset(, 1)
    Set rng = rng.Resize(, rng.Columns.Count - 1)

End Sub
martinitram
  • 31
  • 3