1 ''' // ******************************************************************
   2 ''' // Tools Collection for various tasks
   3 ''' // (c)digital-ecom 2009
   4 ''' // ******************************************************************
   5 
   6 Option Explicit
   7 Option Private Module
   8 
   9 ' //////////////////////////////////////////////////////////////
  10 ''' // GET LAST ROW
  11 ' \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
  12 ''' // This gets the last row filled with data. The fct requires you to handover a specific worksheet before you obtain the last row.
  13 ''' // The fct. remembers the currently active window and activates it again after its job is done. This is required in case the
  14 ''' // worksheet to lookup last row or column is hidden. Otherwise, screen would not be updated anymore.
  15 Function Get_Last_Row(WS As Worksheet) As Long
  16 Dim activeWS As Worksheet
  17     Set activeWS = ActiveSheet
  18 
  19     WS.Activate
  20     On Error Resume Next
  21        Get_Last_Row = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
  22 
  23     If Err.Number = 91 Then
  24         Get_Last_Row = 1
  25         Else
  26     End If
  27     On Error GoTo 0
  28     activeWS.Activate
  29 End Function
  30 
  31 ' //////////////////////////////////////////////////////////////
  32 ''' // GET LAST COLUMN
  33 ' \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
  34 ''' // This gets the last column filled with data. The fct requires you to handover a specific worksheet before you obtain the last row.
  35 ''' // The fct. remembers the currently active window and activates it again after its job is done. This is required in case the
  36 ''' // worksheet to lookup last row or column is hidden. Otherwise, screen would not be updated anymore.
  37 Function Get_Last_Column(WS As Worksheet) As Long
  38 Dim activeWS As Worksheet
  39     Set activeWS = ActiveSheet
  40 
  41     WS.Activate
  42     On Error Resume Next
  43         Get_Last_Column = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
  44 
  45     If Err.Number = 91 Then
  46         Get_Last_Column = 1
  47         Else
  48     End If
  49     On Error GoTo 0
  50     activeWS.Activate
  51 End Function