Find last cell when in filter mode

R

rob nobel

With many thanks to Dave Peterson the following code finds the FIRST cell in
Col G when the cells are in filter mode and even if the cell is normally
unselectable, because it's locked, etc.

I've tried, without success to alter this code to find the LAST Cell that
has
data in column M (when the same situation applies) and then to select the
cell in the same row in Col G.
Rob

Sub FirstEntry()
Dim myRngF As Range
Application.Calculation = xlCalculationManual
Set myRngF = Nothing
On Error Resume Next
With ActiveSheet.AutoFilter.Range
Set myRngF = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.Cells.SpecialCells(xlCellTypeVisible)
End With
On Error GoTo 0

If myRngF Is Nothing Then
MsgBox "No cell to select"
Else
myRngF.Areas(1).Cells(1, 5).Select
End If
Application.Calculation = xlCalculationAutomatic
End Sub
 
D

Dave Peterson

This should get you closer:

Option Explicit

Sub FirstEntry()
Dim myRngF As Range
Application.Calculation = xlCalculationManual
Set myRngF = Nothing
On Error Resume Next
With ActiveSheet.AutoFilter.Range
Set myRngF = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.Cells.SpecialCells(xlCellTypeVisible)
End With
On Error GoTo 0

If myRngF Is Nothing Then
MsgBox "No cell to select"
Else
With myRngF
With .Areas(.Areas.Count)
.Cells(.Cells.Count).Offset(0, 5).Select
End With
End With
End If
Application.Calculation = xlCalculationAutomatic
End Sub

After you filter and take the visible range, each "group" of ranges is called an
area.

This takes the last one .areas(.areas.count), then finds the last cell in that
last area:
.cells(.cells.count)

(I'm not sure how column G and M fit in.)
 
Top