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 ?
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
this method works

Code:
    With Range("A10")
        Range(.Offset(1), Cells(Rows.Count, .Column)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
    End With
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
How is your data being filtered?
If that code puts you on A11 is that row hidden?
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
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 ?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top