Getting the actual used range of a sheet

The ‘used range’ property of a sheet can be really useful to find out what part of an Excel sheet should be processed by a macro but Excel has known bugs to update that property so a reliable way to do it is as below.

Public Sub getUsedRange(ws As Worksheet, ByRef lRow As Long, ByRef lCol As Long)
  If ws.Visible = xlSheetVisible Then
    lRow = ws.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
    lCol = ws.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
  End If

  If ws.UsedRange.Rows.Count > lRow Then lRow = ws.UsedRange.Rows.Count
  If ws.UsedRange.Columns.Count > lCow Then lCol = ws.UsedRange.Rows.Count
End Sub

Keep coding!