-2

Ok so i need to select all the used up ranges except the first range(A range). So I selected all the used ranges using UsedRange.Select, and now i need to unselect the first range. Is there any way to do this?

Divyam Sureka
  • 1
  • 1
  • 1
    https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Tim Stack Apr 24 '19 at 06:40
  • Your question is very unclear, because you didn't tell what *"the first range"* actually is. Please [edit] your question and add a screenshot of your worksheet and explain it as detailed as possible. • Please also include the code you have tried. – Pᴇʜ Apr 24 '19 at 07:11

1 Answers1

1

You could use Intersect to intersect the UsedRange with columns 2 to x where x is the last column of the used range. This way column A is excluded.

But you should really avoid using Select it is a very bad practice and makes your code slow and unreliable: How to avoid using Select in Excel VBA.

Option Explicit

Public Sub SelectUsedRangeExceptColumnA()
    Dim UsedRangeExceptColumnA As Range
    Set UsedRangeExceptColumnA = Intersect(UsedRange, Range(Columns(2), Columns(UsedRange.Columns.Count + UsedRange.Column - 1)))

    If Not UsedRangeExceptColumnA Is Nothing Then
        UsedRangeExceptColumnA.Select
    End If
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • Need to qualify `.UsedRange`. Also is the intention is to exclude `Column(1)` from `UsedRange` then `Intersect ` is not needed. This will set the range `Set UsedRangeExceptColumnA = Range(Columns(2), .Columns(.UsedRange.Columns.Count + .UsedRange.Column - 1))` – EEM Apr 24 '19 at 19:19
  • @EEM No, `Intersect` is needed because otherwise if you do what you suggest it will include the entire columns (not only the used rows). • `UsedRange` must only be qualified if used in a module otherwise it will automatically refer to the sheets scope (which I assumed). But of course full qualified references are preferable. – Pᴇʜ Apr 24 '19 at 20:31
  • *Apologies you are absolutely correct on about the need to intersect.* Also, there was no indication that the procedure was written in a `Sheet.Module` which seldom happens. Btw be aware that the `UsedRange` not necessarily starts at A1… – EEM Apr 24 '19 at 20:58
  • @EEM Well, if he wants to exclude column A as stated in the question then the `UsedRange` *must* start in column A (or if not it doesn't matter, the code will work anyway). Also since `UsedRange.Select` worked for the OP, his code is within a sheet and not a module. – Pᴇʜ Apr 24 '19 at 20:58
  • I also tested, but the rest of the rows were not showing in the screen, as I said before you are absolutely correct, about the need for intersect – EEM Apr 24 '19 at 21:00