ActiveSheet UsedRange Columns Count - 8 what does it mean

Question

what does ActiveSheet UsedRange Columns Count - 8 mean in vba   How does vba know the usedRange

User · Answer

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

User · Answer

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

User · Answer

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      UsedRange -   Draws  a box around the outer-most cells with content inside   Columns -  Selects the entire columns of those cells  Count -  Returns an integer corresponding to how many columns there are  in this selection  - 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

User · Answer

Seems like you want to move around  Try this   ActiveSheet UsedRange select   results in          If you want to move that selection 3 rows up then try this  ActiveSheet UsedRange offset -3  select   does this

User · Answer

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   lt  gt  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  gt  8 Then      Debug Print ActiveSheet UsedRange Columns Count - 8       The above becomes      Debug Print lastCol - 8 End If

[vba] ActiveSheet.UsedRange.Columns.Count - 8 what does it mean?

Examples related to vba

Examples related to excel