Now this question answers elegantly, thanks to Chris Nielsen, see answer below. This is the one that I will use from now on. The solution reliably finds the last cell in the worksheet, even if the cells are hidden by filters, groups, or local row hiding.
The discussion may be informative for some, so I also introduced an optimized version of my own code. It demonstrates how to save and restore filters, uses @Chis ideas to search for the last row, and writes ranges of hidden lines to a short Variant array from which they are finally restored.
A test workbook that explores and tests all of the proposed solutions is also available for download here .
FULL QUESTION AND DISCUSSION, UPDATED
There is a lot of discussion here and elsewhere about finding the latest cells in Excel worksheets. The Range.SpecialCells method has limitations and does not always find the true last cell. This is especially true if Worksheet.AutoFilters is active. The code below solves the problem and returns the correct result, even if the filters are active, the cells are grouped and hidden, and the rows or columns are hidden using Hide / Unhide. However, the method is not simple. Does anyone know of a more reliable method?
By “true last cell” is meant the intersection of the last row containing data or formulas and the last column containing them. Formatting may skip it.
Credits and thanks for the good ideas: read and sancho s .
The code below checks and works in my application in Excel 2010 and requires that Scripting.Runtime be specified in VBIDE. It contains inline comments that document what it does and why. In addition, variable names are intentionally explanatory. Sorry, but that makes them long.
In some cases, it cannot restore the exact rows that were hidden when it was called. I have never done that.
Change 1 to question
Thanks to the 3 respondents on the 3rd. This follows from brettdj , noting the question as already answered. Unfortunately, I do not believe that this is true. At least if only UsedRange cannot be trusted under any circumstances. Although problems with SpecialCells are difficult to reproduce, previous experience with the values provided by SpecialCells does not allow them to rely on them.
brettdj post Returns the range from A1 to the true last used cell , provides a solution, GetRange . This is one of the others, but it seems to be the best. I tested it and all the solutions proposed in this thread. In my tests, none of them can find the last cell when the filter is active, not trusting UsedRange . brettdj , with a high reputation, clearly thinks differently, but it seems to me that I really discovered a real problem.
To demonstrate:
See the following test sheet. All rows and columns are displayed in this view. Note Line 19 with the text "String to hide with filter" in H19. Also note that row 20 on B20 and column J on J11 have information. (Obviously, since this is a test, there is nothing in J20, a cell whose link is the correct answer to the question): 
Tests were performed on the sheet above, but with an active filter (underlined by a red circle in the image below), which removes line 19 from the view. During the tests, the J: K column group was collapsed, but the Row group remained visible for 19:20.
These are the results (the true answer is J20):
Gettrange() brettdj in the answer "Range: A1: B20".TrueLastCell() by Gary Student gives "TRUE last cell is B20", and can sometimes be very expensive, looping at very high rows and columns if UsedRange comes to the end of a mostly empty sheet. (In addition, the screen shown in the answer shows C11 when it should be F11.)GetTrueLastCell(WS) from PatrickK gets the correct answer, J20, but he relies entirely on UsedRange, which, as I understand it, is impossible, or I would never start with that!GetTrueLastCell(WS,,) (for me the code below, although complicated) gives $ J $ 20.
In the unlikely event that this is a specific operating system, my test was run on {you are not allowed to laugh - :)} Vista Home Premium. My excuse is that it is a 64-bit OS on a lightning-fast 8-core machine, even if it is aging. Excel 2010, 32-bit version 14.0.7166.5000.
Change 2 in response
In response to chris neilsen, the request to validate and download the test file is no longer here . Short answer: The problem is too reproducible in Windows 10, running in Office 2013 15.0.4797.1003, and also in Vista - Office 2010. Unfortunately, this is real. The workbook from which the images were made now contains a code for each proposal made here (today March 2, 2016). The shared file is uploaded to OK and reproduces the results on the Windows 7 / Office 2010 machine. To run the tests, find the TestSolutionsProposed module in VBIDE. Debug.Prints from the tests give the same results on W10, W7, Vista and Office 2010 and 2013 (the correct answer is J20):
Brettdj GetRange gives: Range is A1:B20 WS usedrange = $A$1:$K$20 PatrickK GetTrueLastCell gives Found last cell = $K$20 Gary Student TrueLastCell gives: The TRUE last cell is B20 My GetTrueLastCell (with RemoveFiltersAsBoolean = False) gives: Last cell address is B20 My GetTrueLastCell (with RemoveFiltersAsBoolean = True) gives: Last cell address is J20
@brettdj - please, can you restore the status of this question? Of course, it is reproduced by others - how can the results be specific to three separate systems that I can access, but not others? Only removing filters gives the correct answer. Note. The filter must be both present and active in order to show the problem; as loaded, the test workbook is configured to give the results above; having AutoFitlerMode = True not enough. One of the filters must have an active filter criterion - in the example H19 is hidden.
Private Function GetTrueLastCell(ws As Excel.Worksheet, _ Optional lRealLastRow As Long, _ Optional lRealLastColumn As Long, _ Optional RemoveFiltersAsBoolean As Variant = False) As Range 'Purpose: 'Finds the cell at the intersection of the last Row containing any data and the last Column containing any data, ' even if some cells are hidden by Filters, Grouping or are locally Hidden. If there are no filters uses a simple method. 'Returns: the LastCell as a Range; Optionally returns Row and Column indeces. ' If the WS has no data or is not a WS, returns GetTrueLastCell=Nothing & lRealLastRow=0 & RealLastColumn=0 'Developed by extension of ideas from: ' 'Readify' for ideas about saving and restoring filters, ' see: https://stackoverflow.com/questions/9489126/in-excel-vba-how-do-i-save-restore-a-user-defined-filter ' 'Sancho s' 24/12/2014, see https://stackoverflow.com/questions/24612874/finding-the-last-cell-in-an-excel-sheet 'Written by Neil Dunlop 29/2/2016 'History: 2016 03 03 added optimisation of the reapplication of filters following discussion on StackOverFlow wiht ' thanks to Chris Neilsen for review and comments and ideas - see here: ' https://stackoverflow.com/questions/35712424/how-to-find-the-true-last-cell-in-any-worksheet 'Notes: 'This will find the last cell even if rows are Hidden by any means. ' This is partly accomplished by setting Lookin:=xlFormulas, ' and partly by removing and restoring filters that prevent .Find looking in a cell. 'Requirements: ' The reference to Microsoft Scripting Runtime must be present in the VBIDE Tools>References list. Dim FilteredRange As Range, rng As Range Dim wf As Excel.WorksheetFunction Dim MyCriteria1 As Scripting.Dictionary Dim lr As Long, lr2 As Long, lr3 As Long Dim i As Long, j As Long, NumFilters As Long Dim CurrentScreenStatus As Boolean, LastRowHidden As Boolean Dim FilterStore() As Variant, OutlineHiddenRow() As Variant If Not RemoveFiltersAsBoolean Then GoTo JUSTSEARCH CurrentScreenStatus = Excel.Application.ScreenUpdating Excel.Application.ScreenUpdating = False On Error GoTo BADWS If ws.AutoFilterMode Then 'Save all active Filters With ws.AutoFilter If .Filters.Count > 0 Then Set FilteredRange = .Range For i = 1 To .Filters.Count If .Filters(i).On Then NumFilters = NumFilters + 1 ReDim Preserve FilterStore(0 To 4, 1 To NumFilters) FilterStore(0, NumFilters) = i 'The Column to which the filter applies 'If there are only 2 Filters they will be in Criteria1 and Criteria2. 'Above 2 Filters, Criteria1 contains all the filters in a Scripting Dictionary FilterStore(1, NumFilters) = .Filters(i).Count 'The number of conditions active within this filter Select Case .Filters(i).Count Case Is = 1 'There is 1 filter in Criteria1 FilterStore(2, NumFilters) = .Filters(i).Criteria1 Case Is = 2 'There are 2 Filters in Criteria1 and Criteria2 FilterStore(2, NumFilters) = .Filters(i).Criteria1 FilterStore(3, NumFilters) = .Filters(i).Criteria2 Case Else 'There are many filters, they need to be in a Scripting Dictionary in Criteria1 Set MyCriteria1 = CreateObject("Scripting.Dictionary") MyCriteria1.CompareMode = vbTextCompare For j = 1 To .Filters(i).Count MyCriteria1.Add Key:=CStr(j), Item:=.Filters(i).Criteria1(j) Next j Set FilterStore(2, NumFilters) = MyCriteria1 End Select If .Filters(i).Operator Then FilterStore(4, NumFilters) = .Filters(i).Operator End If End If Next i End If ' .Filters.Count > 0 End With 'Check for and store any hidden Outline levels applied to the Rows. 'At this stage the last cell is not known, so the best available estimate , UsedRange, ' is used in the Row loop. The true maximum row number with data may be less than the ' highest row from UsedRange. The code below reduces the maximum estimated efficiently. 'It is believed that UsedRange is never too small; it it were, then the hidden properties ' of some rows may not be stored and will therefore not be restored later. '---------get a true last row--------------------------------------------------------- Set rng = ws.Range(ws.Cells(1, 1), ws.UsedRange.Cells(ws.UsedRange.Cells.CountLarge)) Set wf = Application.WorksheetFunction With rng 'Code from Chris Neilsen lr = .Rows.Count + .Row - 1 lr2 = lr \ 2 lr3 = lr2 \ 2 Do While (lr - lr2) > 30 'Debug.Print "r", lr2, lr If wf.CountA(.Rows(lr2 & ":" & lr)) = 0 Then lr = lr2 lr2 = lr3 lr3 = lr2 \ 2 Else lr3 = lr2 lr2 = (lr + lr2) \ 2 End If Loop For i = lr To 1 Step -1 If wf.CountA(.Rows(i)) <> 0 Then Exit For Next i lr = i End With ' rng '---------record and unhide any hidden Row-------------------------------------------- j = 0 LastRowHidden = False For i = 1 To lr If (Not ws.Rows(i).Hidden And LastRowHidden) Then 'End of a Hidden Rows Range, record the Range Set OutlineHiddenRow(2, j) = ws.Rows(OutlineHiddenRow(1, j) & ":" & i - 1) LastRowHidden = False ElseIf ws.Rows(i).Hidden And Not LastRowHidden Then 'Start of Hidden Rows Range, record the Row j = j + 1 ReDim Preserve OutlineHiddenRow(1 To 2, 1 To j) ' 1 -first row found to be Hidden, 2 - Range of Hidden Rows(i:j) If i <> lr Then OutlineHiddenRow(1, j) = i LastRowHidden = True Else 'Last line in range is hidden all on its own Set OutlineHiddenRow(2, j) = ws.Rows(i & ":" & i) End If ElseIf LastRowHidden And ws.Rows(i).Hidden And i = lr Then 'Special case is for Hidden Range ending on last Row Set OutlineHiddenRow(2, j) = ws.Rows(OutlineHiddenRow(1, j) & ":" & i) Else 'Nothing to do End If Next i NumFilters = j 'Remove the AutoFilter, if any of the filters were On. ' This changes the hidden setting for ALL Rows (but NOT Columns) to visible ' irrespective of the reason for their having become hidden (Filter, Group, local Hide). If NumFilters > 0 Then ws.AutoFilterMode = False End If ' WS.AutoFilterMode JUSTSEARCH: 'Search for the last cell that contains any sort of 'formula'. 'xlPrevious ensures that the search starts from the end of the last Row or Column (it the next cell after (1,1)). 'LookIn:=xlFormulas ensures that the search includes a search across Hidden data. ' However, if ANY filters are active the search NO LONGER LOOKS IN HIDDEN CELLS. Also the reverse search ' starts at the end of the column or row containing (1,1) instead of starting at the very end row and column. ' This is why all filters have to be stored, removed and reapplied to find the correct end cell. lRealLastColumn = ws.Cells.Find(What:="*", _ After:=ws.Cells(1, 1), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False, _ MatchByte:=False, _ SearchFormat:=False).Column If lr = 0 Then lRealLastRow = ws.Cells.Find(What:="*", _ After:=ws.Cells(1, 1), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False, _ MatchByte:=False, _ SearchFormat:=False).Row Else lRealLastRow = lr End If Set GetTrueLastCell = ws.Cells(lRealLastRow, lRealLastColumn) 'Restore the saved Filters to their Rows. If NumFilters Then 'Restore the original AutoFilter settings FilteredRange.AutoFilter With ws.AutoFilter For i = 1 To UBound(FilterStore, 2) If FilterStore(4, i) Then 'There is an Operator If FilterStore(1, i) > 2 Then 'There is a ScriptingDictionary for Criteria1 FilteredRange.AutoFilter Field:=FilterStore(0, i), _ Criteria1:=FilterStore(2, i).Items, _ Criteria2:=FilterStore(3, i), _ Operator:=FilterStore(4, i) Else 'Criteria 1 is a string FilteredRange.AutoFilter Field:=FilterStore(0, i), _ Criteria1:=FilterStore(2, i), _ Criteria2:=FilterStore(3, i), _ Operator:=FilterStore(4, i) End If Else 'No Operator If FilterStore(1, i) > 2 Then 'There is a ScriptingDictionary for Criteria1 FilteredRange.AutoFilter Field:=FilterStore(0, i), _ Criteria1:=FilterStore(2, i).Items Else 'Criteria 1 is a string FilteredRange.AutoFilter Field:=FilterStore(0, i), _ Criteria1:=FilterStore(2, i) End If End If Next i End With End If ' NumFilters If NumFilters > 0 Then 'Restore the Hidden status of any Rows that were revealed by setting WS.AutoFilterMode = False. 'Rows, not columns are filtered. Columns' Hidden status does not need to be restored ' because AutoFilter does not unhide Columns. For i = 1 To NumFilters OutlineHiddenRow(2, i).Hidden = True 'Restore the hidden property to the stored Row Range Next i End If ' NumFilters > 0 GoTo ENDFUNCTION BADWS: lRealLastRow = 0 lRealLastColumn = 0 Set GetTrueLastCell = Nothing ENDFUNCTION: Set wf = Nothing Set MyCriteria1 = Nothing Set FilteredRange = Nothing Excel.Application.ScreenUpdating = CurrentScreenStatus End Function