Offest (x,y) in a filtered sheet

[kooocko]

Hi

I need a simple macro that does this:

ActiveCell.Offset(5, -5).Range("A1").Select

The problem is that my sheet is filtered and this code counts the invisible rows as well. What do I need to add to the code to consider only visible rows?
Thanks

[nathansav]

.SpecialCells(xlCellTypeVisible)

[kooocko]

I Googled for an answer but I only found results that are written for going a single row down in a filtered sheet. Your solution is not working unfortunately...

[nathansav]

Activesheet.SpecialCells(xlCellTypeVisible).Offset(5, -5).Select

Havent tested. Google's a great way to find these things out.

About 210,000 results (0.36 seconds) for select visible cells excel vba

[kooocko]

Googled for an answer but I only found results that are written for going a single row down in a filtered sheet. Your solution is not working unfortunately...

[ragulduy]

Quite an interesting problem, not something I've tried to do. Hopefully there's a "built-in" way but one thing I thought of would be like:

Please Login or Register  to view this content.

although I have only set that off to do the rows, not the columns as well.

[kooocko]

OK, I just tried this and it selects the active cell along with 4 visible cells below it. It is a step in the right direction, but all I need is to position the "cursor" (active cell) 5 rows down and 5 columns to the left, counting only the visible cells. The end result should be a single selected cell.

[ragulduy]

@nathansav, I don't think that would work. It would try and offset the entire range that you are returning with the .specialcells method to, so would try and select a column -5 from the first visible cell, in the range of visible cells, which would cause an error.

[kooocko]

@ragulduy, could you please make necessary changes to your code so that it does what I asked?

[ragulduy]

hi, I gave you a method for how to do it, you will need to extend it to your particular problem. The idea is to offset one cell at a time and count how many of those cells are hidden/visible, then loop until you have moved enough. So you will need a second loop to do the same as the first but for columns.

[kooocko]

Columns are not a problem - I can just use the regular ActiveCell.Offset(0, -5) after I have positioned on the 5th row below the original one. The problem is how do I get 5 active rows down and stay on the 5th using the code?

[ragulduy]

In the code, rng2 represents the "5 active rows down" cell, so:

Please Login or Register  to view this content.

[kooocko]

I'm getting this when trying to run:

error.jpg

[ragulduy]

It works fine for me, providing the selected cell is not in columns A-E (as you are offsetting by -5 columns). With, for example, A1 selected, I get the same error as you.

Try selecting a cell like K5 and trying again.

[kooocko]

Working \o/

The only thing I had to edit was "Do Until count = 5" - I set the counter = 6.
Thanks a lot ragulduy
One more thing - what is the best way to insert the code into the sheet? I just record a simple macro, set the keyboard shortcut and then edit the code.

[ragulduy]

Well nothing wrong with how you do it but wihout recording the macro:
-Press ALT+F11 to open the vba editor.
-Click insert->New module and copy the code into the empty module window with an appropraite name for the Sub, for example Sub My_Offset_Macro() instead of Sub Macro_1()
-Return to the excel workbook window and press ALT+F8, find your macro in the list, select it and press options and then you can assign a shortcut key.

[kooocko]

Thanks a lot, I owe you one