Find last cell when in filter mode

  • Thread starter Thread starter rob nobel
  • Start date Start date
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
 
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.)
 
Thanks Dave. Unfortunately I think what I'm trying to do may be too hard to
explain without you seeing the workbook so if this is too hard I'll leave as
is as it's not working too bad.
Your code just goes to the last cell (not the last cell that has data in
it).

Rob
 
If it gets you to the lastcell, you can use go up from there. Check to see if
it has a value and if it's row is hidden. (I think you did something like this
before.)
 
Back
Top