Getting the actual usedrange

Question

I have a Excel worksheet that has a button   When I call the usedRange   function  the range it returns includes the button part    Is there anyway I can just get actual used range that contains data

User · Answer

This function returns the actual used range to the lower right limit  It returns  Nothing  if the sheet is empty    2020-01-26 Function fUsedRange   As Range Dim lngLastRow As Long Dim lngLastCol As Long Dim rngLastCell As Range     On Error Resume Next     Set rngLastCell   ActiveSheet Cells Find      searchorder  xlByRows  searchdirection  xlPrevious      If rngLastCell Is Nothing Then   look for data backwards in rows         Set fUsedRange   Nothing         Exit Function     Else         lngLastRow   rngLastCell Row     End If     Set rngLastCell   ActiveSheet Cells Find      searchorder  xlByColumns  searchdirection  xlPrevious      If rngLastCell Is Nothing Then   look for data backwards in columns         Set fUsedRange   Nothing         Exit Function     Else         lngLastCol   rngLastCell Column     End If     Set fUsedRange   ActiveSheet Range Cells 1  1   Cells lngLastRow  lngLastCol     set up range End Function

User · Answer

I use the following vba code to determine the entire used rows range for the worksheet to then shorten the selected range of a column       Set rUsedRowRange   Selection Worksheet UsedRange Columns        Selection Column - Selection Worksheet UsedRange Column   1    Also works the other way around       Set rUsedColumnRange   Selection Worksheet UsedRange Rows        Selection Row - Selection Worksheet UsedRange Row   1

User · Answer

This function gives all 4 limits of the used range   Function FindUsedRangeLimits       Set Sheet   ActiveSheet     Sheet UsedRange Select        Display the range s rows and columns      row min   Sheet UsedRange Row     row max   row min   Sheet UsedRange Rows Count - 1     col min   Sheet UsedRange Column     col max   col min   Sheet UsedRange Columns Count - 1      MsgBox  Rows    amp  row min  amp    -    amp  row max  amp  vbCrLf  amp                Columns     amp  col min  amp    -    amp  col max     LastCellBeforeBlankInColumn   True End Function

User · Answer

Readify made a very complete answer  Yet  I wanted to add the End statement  you can use   Find the last used cell  before a blank in a Column   Sub LastCellBeforeBlankInColumn   Range  A1   End xldown  Select End Sub   Find the very last used cell in a Column   Sub LastCellInColumn   Range  A   amp  Rows Count  End xlup  Select End Sub   Find the last cell  before a blank in a Row   Sub LastCellBeforeBlankInRow   Range  A1   End xlToRight  Select End Sub   Find the very last used cell in a Row   Sub LastCellInRow   Range  IV1   End xlToLeft  Select End Sub   See here for more information  and the explanation why xlCellTypeLastCell is not very reliable

User · Answer

Here s another one  It looks for the first and last non empty cell and builds are range from those  This also handles cases where your data is not rectangular and does not start in A1  Furthermore it handles merged cells as well  which  Find skips when executed from a macro  used on  Cells on a worksheet  Function getUsedRange ByRef sheet As Worksheet  As Range   finds used range by looking for non empty cells   works around bug in  Find that skips merged cells   by starting at with the UsedRange  that may be too big     credit to https   contexturesblog com archives 2012 03 01 select-actual-used-range-in-excel-sheet    for the  Find commands  Dim excelsUsedRange As Range Dim lastRow As Long Dim lastCol As Long Dim lastCell As Range Dim firstRow As Long Dim firstCol As Long Dim firstCell As Range  Set excelsUsedRange   ActiveSheet UsedRange  lastRow   excelsUsedRange Find What   quot   quot                                    LookIn  xlValues  SearchOrder  xlRows                                   SearchDirection  xlPrevious  Row lastCol   excelsUsedRange Find What   quot   quot                                    LookIn  xlValues  SearchOrder  xlByColumns                                   SearchDirection  xlPrevious  Column Set lastCell   sheet Cells lastRow  lastCol   firstRow   excelsUsedRange Find What   quot   quot   After  lastCell                                    LookIn  xlValues  SearchOrder  xlRows                                    SearchDirection  xlNext  Row firstCol   excelsUsedRange Find What   quot   quot   After  lastCell                                    LookIn  xlValues  SearchOrder  xlByColumns                                    SearchDirection  xlNext  Row Set firstCell   sheet Cells firstRow  firstCol  Set getUsedRange   sheet Range firstCell  lastCell  End Function

User · Answer

Here s a pair of functions to return the last row and col of a worksheet  based on Reafidy s solution above       Function LastRow ws As Object  As Long          Dim rLastCell As Object         On Error GoTo ErrHan         Set rLastCell   ws Cells Find      ws Cells 1  1       xlByRows                                          xlPrevious          LastRow   rLastCell Row      ErrExit          Exit Function      ErrHan          MsgBox  Error    amp  Err Number  amp        amp  Err Description                   vbExclamation   LastRow            Resume ErrExit      End Function      Function LastCol ws As Object  As Long          Dim rLastCell As Object         On Error GoTo ErrHan         Set rLastCell   ws Cells Find      ws Cells 1  1       xlByColumns                                          xlPrevious          LastCol   rLastCell Column      ErrExit          Exit Function      ErrHan          MsgBox  Error    amp  Err Number  amp        amp  Err Description                   vbExclamation   LastRow            Resume ErrExit      End Function

User · Answer

Public Sub FindTrueUsedRange RowLast As Long  ColLast As Long      Application EnableEvents   False     Application ScreenUpdating   False     RowLast   0     ColLast   0     ActiveSheet UsedRange Select     Cells 1  1  Activate     Selection End xlDown  Select     Selection End xlDown  Select     On Error GoTo -1  On Error GoTo Quit     Cells Find What       LookIn  xlFormulas  LookAt  xlWhole  SearchOrder  xlByRows  SearchDirection  xlPrevious  Activate     On Error GoTo -1  On Error GoTo 0     RowLast   Selection Row     Cells 1  1  Activate     Selection End xlToRight  Select     Selection End xlToRight  Select     Cells Find What       LookIn  xlFormulas  LookAt  xlWhole  SearchOrder  xlByColumns  SearchDirection  xlPrevious  Activate     ColLast   Selection Column Quit      Application ScreenUpdating   True     Application EnableEvents   True     On Error GoTo -1  On Error GoTo 0 End Sub

User · Answer

What sort of button   neither a Forms Control nor an ActiveX control should affect the used range   It is a known problem that excel does not keep track of the used range very well   Any reference to the used range via VBA will reset the value to the current used range   So try running this sub procedure   Sub ResetUsedRng       Application ActiveSheet UsedRange  End Sub    Failing that you may well have some formatting hanging round   Try clearing deleting all the cells after your last row   Regarding the above also see   Excel Developer Tip  Another method to find the last used cell       Dim rLastCell As Range      Set rLastCell   ActiveSheet Cells Find What       After   Cells 1  1   LookIn  xlFormulas  LookAt         xlPart  SearchOrder  xlByRows  SearchDirection  xlPrevious  MatchCase  False    Change the search direction to find the first used cell

User · Answer

Timings on Excel 2013 fairly slow machine with a big bad used range million rows   26ms  Cells Find xlPrevious method  as above   0 4ms Sheet UsedRange  just call it   0 14ms Counta binary search   0 4ms Used Range to start search  12 CountA calls   So the Find xlPrevious is quite slow if that is of concern   The CountA binary search approach is to first do a Used Range   Then chop the range in half and see if there are any non-empty cells in the bottom half  and then halve again as needed   It is tricky to get right

[excel] Getting the actual usedrange

Examples related to excel

Examples related to vba