How to go to the next visible cell in a filtered range using macro?

[nanmohandas]

Hi,

I want to go to the next visible cell of a filtered range using a macro code. For example: my active cell is a1 and the filtered visible cell is a5, I want the macro to select a5. (Note: Its not a5 all the time).

offst goes to a2. so please help me with this. Thanks.


Cheers.

[charles]

Hi,

See if this simple code helps you.
No matter what the last row is this code should display the row.

Code: Select all

Sub Last_Visible_Cell()
MsgBox "Last visible cell now is " & Range("A1").End(xlDown).Row
End Sub


[nanmohandas]

Hi,

Thanks for the reply, but this doesn't help me anyway. my requirements are totally different. hoping for better answers. thanks.

[Sisyphus]

Hello nanmohandas,
Please try this code:

Code: Select all

Option Explicit

Private Sub Worksheet_Calculate()
    Dim C As Long
    Dim R As Long

    R = 2          ' First data row on the sheet
    With ActiveCell
        If Rows(.Row).RowHeight > 0 Then Exit Sub
        C = .Column
    End With

    Do While Rows(R).RowHeight = 0
        R = R + 1
    Loop
    Cells(r, C).Select
End Sub

Paste this code in the code module of the worksheet on which you need the action.
Please enter the number of the first data row (first row under any existing header) against R at the top of the code, replacing the default of 2.

This code will not change the previously selected column, nor will it take action if the ActiveCell was in a visible row. However, if the ActiveCell is invisible the first visible cell in the selected column will be selected. Therefore it is possible but not necessary that the selection jumps from way down in the sheet to the visible row at the top.

[charles]

Hi,


This is another version of "Sis" code.
Hope this a "Better Answer"

Code: Select all

Sub FirstVisibleCell()
    With Worksheets("Sheet1").AutoFilter.Range
        .SpecialCells(xlCellTypeVisible).Areas(2)(1, 1).Select
    End With
End Sub


[nanmohandas]

Hey Sisyphus & Charles,

Thanks a lot Both. Charles your code is working. Sisyphus it's not . (may be I didnt get it right, I guess ) but I got what I need.
The one thing in Charles code is, if the filtered cell is row 2, it debug's as there are no invisible cells in between.so can you please share anything for that

Cheers. Have a good day.

[Sisyphus]

Hi,
I tested my code. It is running. However, my laptop had no Internet access. So I manually copied the code to an iPad. Perhaps there was a typo somewhere. I will check it later.
The problem isn't with the code running or not but to get it to run automatically. I used the Calculate event which is triggered by a change of filter, among others. My code, therefore, is careful not to change the selection if it is run by some unrelated reason.
Charles, in order to let your code run automatically on the Calculate event you should maintain a permanent copy of the existing filter and allow your selection changer only to run if the filter was changed. If you incorporate this feature the difference between your code and mine would be that yours always selects the first visible while mine would not change anything if a previous selection is still visible.

[Sisyphus]

Hello Charles,
Of course, SpecialCells(xlCellsTypeVisible) should prove to be the better code for this application. It was the one I had wanted, too, but I could neither remember nor find it yesterday. So, Thank you for pointing it out to me.

[Sisyphus]

Hi,
I checked the code I posted and confirm that it works perfectly. However, one should bear in mind that it is triggered by the worksheet's Calculate event which will not occur if there isn't anything to calculate in the worksheet. If the worksheet where it is to be deployed has no formulas I would, indeed, have to come up with another solution.
By creating code that won't do anything while the selected cell isn't hidden, and very little if it is, I have avoided the hassle of keeping track of the filter. Charles' code, originally my intention, too, could have been incorporated in the present design. However, upon review, I have decided against this option. It requires setting of a range and the resulting code would be more voluminous that what he have. A likely advantage in speed is too insignificant to consider.
Therefore I have not amended the code already posted above.