Offset select upwards without looping without selecting hidden cells?

[MichaelRSnow]

Hi

I've googled this for over 2 hours now and cannot find a solution that fits the remit and I've tried and failed numerous times to create something that remotely functions?

Simple ask, my sheet has c.150k+ rows of data, so looping out of the question, which has been filtered to avoid the need loop

Starting a the bottom visible cell (column N, Row XX) I just want to Select/Activate the next visible row above it, without looping through all the hidden rows in between?

Is that possible?

 

[skillilea]

Does this get you close?

Sub SelectLastVisibleRowAbove()
    Dim currentCell As Range
    Dim lastVisibleCell As Range

    Set currentCell = Range("B2")

    On Error Resume Next
    Set lastVisibleCell = currentCell.EntireColumn.Find("*", , xlValues, xlWhole, , xlPrevious, , True)
    On Error GoTo 0

    If Not lastVisibleCell Is Nothing Then
        lastVisibleCell.EntireRow.Select
    End If
End Sub

 

[MichaelRSnow]

Does this get you close?

Sub SelectLastVisibleRowAbove()
    Dim currentCell As Range
    Dim lastVisibleCell As Range

    Set currentCell = Range("B2")

    On Error Resume Next
    Set lastVisibleCell = currentCell.EntireColumn.Find("*", , xlValues, xlWhole, , xlPrevious, , True)
    On Error GoTo 0

    If Not lastVisibleCell Is Nothing Then
        lastVisibleCell.EntireRow.Select
    End If
End Sub

Sorry should have been clearer, where I said row above I meant the cell above, I've tweaked your code to select the cell, although selecting the last visible cell wasn't the issue.
Once I have selected the last visible cell I then need the code to select the next visible cell above the original cell selected? That is the tricky part, without looping through all the cells inbetween

Sub SelectLastVisibleRowAbove()
    Dim currentCell As Range
    Dim lastVisibleCell As Range
    Dim LRow As Long
   
    With Sheets("Data")
            LRow = .Cells(.Rows.Count, "N").End(xlUp).Row '
    End With

    Set currentCell = Range("N" & LRow)

    On Error Resume Next
    Set lastVisibleCell = currentCell.EntireColumn.Find("*", , xlValues, xlWhole, , xlPrevious, , True)
    On Error GoTo 0

    If Not lastVisibleCell Is Nothing Then
        lastVisibleCell.Select
    End If
End Sub

 

[skillilea]

I'd love to see a picture of what you are trying to select.

Just curious I know you don't want to loop. Have you tried putting the data into an array and looping that. Is it still too slow?

 

[MichaelRSnow]

Here yo go, If you are on cell N140718 (starting point), filter is on, I need it to select the cell N37290 without looping, like it would if you just used the up arrow key (if possible avoiding using send keys). Does that make sense?

 

[skillilea]

OK here is my example

Two subs gives first and last, you could do math on the rows to select if this is not what you want maybe???
Or am I still not understanding

Sub SelectLastVisibleRow()
    Dim currentCell As Range
    Dim lastVisibleCell As Range
    Dim LRow As Long
   
    With Sheets("Sheet4")
            LRow = .Cells(.Rows.Count, "B").End(xlUp).Row '
    End With

    Set currentCell = Range("B" & LRow)

    On Error Resume Next
    Set lastVisibleCell = currentCell.EntireColumn.Find("*", , xlValues, xlWhole, , xlPrevious, , True)
    On Error GoTo 0

    If Not lastVisibleCell Is Nothing Then
        lastVisibleCell.Select
    End If
End Sub

Sub SelectFirstVisibleRow()
    Dim firstVisibleCell As Range

    On Error Resume Next
    Set firstVisibleCell = Sheets("Sheet4").Columns("B").Find("*", , xlValues, xlWhole)
    On Error GoTo 0

    If Not firstVisibleCell Is Nothing Then
        firstVisibleCell.Select
    End If
End Sub

MrExcel.xlsm
	A	B	C
1		num
33		18.33333
34		18.83333
35		19.33333
36		19.83333
71
Sheet4

 

[MichaelRSnow]

I'll take a look, the example image I shared had two rows unfiltered, but there could be 3 or 10 rows visible, the requirements is the same though to just go up 1 visible cell to the next row above

 

[Shloime]

Findnext will go to the next visible cell see below (i used here Findprevious as i assume that's what you are looking for):

Sub SelectLastVisibleRowAbove()
Dim currentCell As Range
Dim lastVisibleCell As Range
Dim NextCell As Range
Dim LRow As Long

With Sheets("Data")
LRow = .Cells(.Rows.Count, "N").End(xlUp).Row '
End With

Set currentCell = Range("N" & LRow)

On Error Resume Next
Set lastVisibleCell = currentCell.EntireColumn.Find("*", , xlValues, xlWhole, , xlPrevious, , True)
On Error GoTo 0

If Not lastVisibleCell Is Nothing Then
On Error Resume Next
lastVisibleCell.Select
On Error GoTo 0
Set NextCell = currentCell.EntireColumn.FindPrevious(after:=ActiveCell)
While NextCell.Row < lastVisibleCell.Row
NextCell.Select
Set NextCell = currentCell.EntireColumn.FindPrevious(after:=ActiveCell)
Wend
End If

'

End Sub

 

[rollis13]

Hi to all.
This is the best I can code at the moment, but it still loops via the Goto statement:

Option Explicit
Sub GotoVisibleCellAbove()
    'check if selected cell is in used range of column N, if not then select last cell of usedrange in column N
    With ActiveCell
        If .Column <> 14 Then
            'if not column N move to its last cell
            Range("N" & Rows.Count).End(xlUp).Select
        Else
            'if already on top or below last cell of used range move to last cell
            If .Row = 1 Or .Row > Range("N" & Rows.Count).End(xlUp).Row Then Range("N" & Rows.Count).End(xlUp).Select
        End If
    End With
CheckNewSelection:
    'check if above cell is hidden
    With Selection
        If .Row > 1 Then
            'if above cell is a hidden cell
            If .Offset(-1, 0).EntireRow.Hidden = True Then
                'move to new cell above
                .Offset(-1, 0).Select
                'then check new selected cell
                GoTo CheckNewSelection
            Else
                'or if visible select it
                .Offset(-1, 0).Select
            End If
        End If
    End With
End Sub

 

[MichaelRSnow]

Hi to all.
This is the best I can code at the moment, but it still loops via the Goto statement:

Option Explicit
Sub GotoVisibleCellAbove()
    'check if selected cell is in used range of column N, if not then select last cell of usedrange in column N
    With ActiveCell
        If .Column <> 14 Then
            'if not column N move to its last cell
            Range("N" & Rows.Count).End(xlUp).Select
        Else
            'if already on top or below last cell of used range move to last cell
            If .Row = 1 Or .Row > Range("N" & Rows.Count).End(xlUp).Row Then Range("N" & Rows.Count).End(xlUp).Select
        End If
    End With
CheckNewSelection:
    'check if above cell is hidden
    With Selection
        If .Row > 1 Then
            'if above cell is a hidden cell
            If .Offset(-1, 0).EntireRow.Hidden = True Then
                'move to new cell above
                .Offset(-1, 0).Select
                'then check new selected cell
                GoTo CheckNewSelection
            Else
                'or if visible select it
                .Offset(-1, 0).Select
            End If
        End If
    End With
End Sub

BINGO, you had a piece of code that work perfectly,
I've commented out the code that isn't required and just now defined the ActiveCell in this example as easier when starting mid way through the data set, it will select the last cell in the final version
This code then moves up from the active cell to the previous visible cell, if I run the code again it goes up another visible row each time I run the code

Sub SelectLastVisibleRowAbove()
Dim currentCell As Range
Dim lastVisibleCell As Range
Dim NextCell As Range
Dim LRow As Long

With Sheets("Data")
LRow = ActiveCell.Row
End With

Set currentCell = Range("N" & LRow)

'On Error Resume Next
'Set lastVisibleCell = currentCell.EntireColumn.Find("*", , xlValues, xlWhole, , xlPrevious, , True)
'On Error GoTo 0
'
'If Not lastVisibleCell Is Nothing Then
'On Error Resume Next
'lastVisibleCell.Select
'On Error GoTo 0
Set NextCell = currentCell.EntireColumn.FindPrevious(after:=ActiveCell)

NextCell.Select

'While NextCell.Row < lastVisibleCell.Row
'NextCell.Select
'Set NextCell = currentCell.EntireColumn.FindPrevious(after:=ActiveCell)
'Wend
'End If

End Sub