14

what does ActiveSheet.UsedRange.Columns.Count - 8 mean in vba?

How does vba know the usedRange?

strangeQuirks
  • 4,761
  • 9
  • 40
  • 67

5 Answers5

19

Here's the exact definition of UsedRange (MSDN reference) :

Every Worksheet object has a UsedRange property that returns a Range object representing the area of a worksheet that is being used. The UsedRange property represents the area described by the farthest upper-left and farthest lower-right nonempty cells in a worksheet and includes all cells in between.

So basically, what that line does is :

  1. .UsedRange -> "Draws" a box around the outer-most cells with content inside.
  2. .Columns -> Selects the entire columns of those cells
  3. .Count -> Returns an integer corresponding to how many columns there are (in this selection)
  4. - 8 -> Subtracts 8 from the previous integer.

I assume VBA calculates the UsedRange by finding the non-empty cells with lowest and highest index values.

Most likely, you're getting an error because the number of lines in your range is smaller than 3, and therefore the number returned is negative.

Bernard Saucier
  • 2,240
  • 1
  • 19
  • 28
  • I have done this but it does not do the minus 3: Dim LR As Long, LC As Long LC = ActiveSheet.UsedRange.Columns.Count - 8 LR = ActiveSheet.UsedRange.Rows.Count - 3 Range("A2", Cells(LR, LC)).copy Range("A2").Select – strangeQuirks Jan 16 '14 at 20:42
  • 1
    I suggest you put this code in your question, and make it clear what you're trying to do. – Doug Glancy Jan 16 '14 at 21:10
  • 2
    Sure, also learn how to debug VBA using the immediate window, debug.print, locals window, etc... It will help you solving a world of problems. – html_programmer Jan 16 '14 at 22:14
  • 1
    @KimGysen, a world of problems is an understatement. :p – Bernard Saucier Jan 16 '14 at 22:33
  • + 1 Nicely explained :) However this `I assume VBA calculates the UsedRange by finding the non-empty cells with lowest and highest index values.` is not entirely true. Excel behaves erratically when it comes to calculating `UsedRange` and hence it is highly unreliable as explained in my post above. – Siddharth Rout Jan 17 '14 at 04:17
5

BernardSaucier has already given you an answer. My post is not an answer but an explanation as to why you shouldn't be using UsedRange.

UsedRange is highly unreliable as shown HERE

To find the last column which has data, use .Find and then subtract from it.

With Sheets("Sheet1")
    If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
        lastCol = .Cells.Find(What:="*", _
                      After:=.Range("A1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByColumns, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False).Column
    Else
        lastCol = 1
    End If
End With

If lastCol > 8 Then
    'Debug.Print ActiveSheet.UsedRange.Columns.Count - 8

    'The above becomes

    Debug.Print lastCol - 8
End If
Community
  • 1
  • 1
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • +1....I used `usedRange` in my example cos Bernard did - his fault :) Shame it misbehaves - I've encountered this myself and don't use it - will it, or has it been fixed ? – whytheq Feb 01 '14 at 17:29
  • @whytheq: My personal opinion? One should forget that there is a word called `usedrange` :P – Siddharth Rout Feb 01 '14 at 22:29
  • I think it is potentially very powerful and could save on a lot of `rows.count).end(xltosomeplace).row.etc.etc` type code ....but it would need to work – whytheq Feb 02 '14 at 07:48
1

Seems like you want to move around. Try this:

ActiveSheet.UsedRange.select

results in....

enter image description here

If you want to move that selection 3 rows up then try this

ActiveSheet.UsedRange.offset(-3).select

does this...

enter image description here

whytheq
  • 34,466
  • 65
  • 172
  • 267
0

I think if you try:

Sub Macro3()
a = ActiveSheet.UsedRange.Columns.Count - 3
End Sub

with a watch on a you will see it does make a difference.

pnuts
  • 58,317
  • 11
  • 87
  • 139
0

UsedRange represents not only nonempty cells, but also formatted cells without any value. And that's why you should be very vigilant.

Leszek
  • 1
  • 1