Using Offset on Visible cells only

[KNKN9]

Hi,

I have the code

ActiveSheet.Range("A10").Offset(1, 0).Select

But this does not work for me as my data is filtered. I do not want to unfiltered the data. But how can I use offset on functioning with on the cells that are visible ?

 

[Yongle]

this method works

    With Range("A10")
        Range(.Offset(1), Cells(Rows.Count, .Column)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
    End With

 

[KNKN9]

Hi ,

I seem to get the code 'Application defined or object defined error' when I run the code below.

this method works

    With Range("A10")
        Range(.Offset(1), Cells(Rows.Count, .Column)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
    End With

 

[Yongle]

Try this then
- it also works for me

    Dim cel As Range
    Set cel = ActiveSheet.Range("A10")
    Range(cel.Offset(1), cel.Parent.Cells(Rows.Count, cel.Column)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select

 

[KNKN9]

Unfortunately , this does not work for me. once the code is run it stays at the cell below Range("A10")

Try this then
- it also works for me

    Dim cel As Range
    Set cel = ActiveSheet.Range("A10")
    Range(cel.Offset(1), cel.Parent.Cells(Rows.Count, cel.Column)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select

 

[Fluff]

How is your data being filtered?
If that code puts you on A11 is that row hidden?

 

[mikerickson]

There is the brute force approch

Dim myCell as Range

Range("A10").Select

Set myCell = ActiveCell
Do
    Set myCell = myCell.Offset(1, 0)
Loop Until (myCell.EntireRow.Hidden = False) 
MyCell.Select

 

[KNKN9]

No the code works only for the first offset for the first visible cell. i.e ( It will drop down to range(A19).

- Data filtered showing all data that has a 'Y' associated to it in a hidden column.

How is your data being filtered?
If that code puts you on A11 is that row hidden?

 

[Yongle]

You asked for a "single" result.
You did not mention anything about the "first" or "next" filtered cell. The code I gave you was for one action, not a loop. But you appear to be attempting some sort of loop.

What exactly are you trying to achieve ?

 

[Fluff]

In that case you need to explain what you are trying to do, as the code that Yongle posted in post#4 is selecting the first visible cell below A10, which is what your code was trying to do.