Using Offset on Visible cells only

KNKN9

Board Regular
Joined
Mar 27, 2017
Messages
92
Hi,

I have the code

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 ?
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,636
Office Version
365
Platform
Windows
this method works

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

KNKN9

Board Regular
Joined
Mar 27, 2017
Messages
92
Hi ,

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

this method works

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

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,636
Office Version
365
Platform
Windows
Try this then
- it also works for me

Code:
    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
 
Last edited:

KNKN9

Board Regular
Joined
Mar 27, 2017
Messages
92
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

Code:
    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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,572
Office Version
365
Platform
Windows
How is your data being filtered?
If that code puts you on A11 is that row hidden?
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,257
There is the brute force approch

Code:
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
 
Last edited:

KNKN9

Board Regular
Joined
Mar 27, 2017
Messages
92
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

Well-known Member
Joined
Mar 11, 2015
Messages
5,636
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,572
Office Version
365
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,393
Messages
5,444,213
Members
405,274
Latest member
go2nivas

This Week's Hot Topics

Top