Keep Selected Cell/Row Visible After Changing Filter

[mjones]

Hi All,

Trying to find a row in an 8000-row spreadsheet, I filter to find what I need in row 6690.

Great. Select the first cell in that row.

Now I need more rows under this row, so I change the filter to open those rows too.

And voila, I see rows 742 to 762.

Prior to refiltering, I needed to memorize row number 6692 so I can now scroll way down to find it again. It’s annoying when one needs to do this hundreds of times.

Is there a way to keep my selected row 6690 visible after changing filtered rows? Maybe not, but then again, people on this board have come up with amazing ideas, so I ask.

Thanks again!

Michele

 

[mjones]

Sorry typo. Should say 'memorize row number 6690'.

 

[Burrgogi]

I'm a bit unclear on your question. It would help to know what criteria(s) you are using to filter 1st time around, and then refiltering after you've unfiltered. I have a vague idea of what you're trying to say but not 100% sure. Can you take a screenshot of this filtering process or upload a small sample of your workbook?

 

[mjones]

Hi,

I’ll try but please understand that the rows numbers are different as I’ve changed things.

In the first Filter1 image, I’ve filtered by E-N in column K. I found what I needed, which was September in B5546. I selected it.

In the second Filter2 image, I’ve added blanks to the filter. Now I’m seeing rows 4232 and below making row 5546 (where I want to be) way below so I have to go way down to find it. This is not that far but at times it’s almost 8000 rows down.

I’d like not to have to find September again and have the screen stay on the selected cell. Is this possible?

Thank you again!

Michele

 

[Burrgogi]

My first inclination would be to create a subroutine that creates a named range for your "September" in cell B5546. (Let's say "Ice_Cream" because it's summer time and I can't stay away from that stuff, lol). BTW, you can name that whatever you wish but it cannot contain spaces. Then insert new rows as needed which you can do quite easily since we gave you the code in your previous thread. Or you can merge that code into a new macro that combines everything. Then after the new rows are inserted, jump back to the named range "Ice_Cream" which in this case would be cell B5546, then delete the named range so you can assign it to another cell for other searches you may want to do in the future. Does this fit the basic idea of what you're trying to do?

 

[mjones]

Not quite the idea because I look for different things/rows all the time. I might search for that row, which changes all the time because I'm constantly adding more rows, a maximum of 5 times before everything I need to do to that row (and the group of rows below it) is done. So you see that making a macro for this specific situation wouldn't be used after a few times.

The annoyance is that after filtering or unfiltering, I'd hope to still see the cell I've selected still on the screen and not have it disappear thousands of rows up or down. I hope that explains things better.

 

[Burrgogi]

I didn't quite explain things correctly when I commented earlier:

...Then after the new rows are inserted, jump back to the named range "Ice_Cream" which in this case would be cell B5546,

That is an incorrect statement because the macro can jump back to the named range "Ice_Cream" but I should not have said back to cell B5546 because new rows are added (or deleted for that matter).

 

[Burrgogi]

The annoyance is that after filtering or unfiltering, I'd hope to still see the cell I've selected still on the screen and not have it disappear thousands of rows up or down. I hope that explains things better.

OK, now I understand better. You can just use split scroll.

Example here:

How to Use Split Screen in Excel Worksheets - Automate Excel

This tutorial demonstrates how to use split screen in Excel. Split Screen The split screen option in Excel is useful when you have a large set of data you want to scroll through horizontally or vertically. In that case, Excel allows you to split the screen into four parts (panes) and scroll...

www.automateexcel.com

 

[mjones]

Ah ha. That's great!! Thanks so much! Didn't know about that feature. Or maybe it's been so long I forgot since I've been working with Excel since it's inception.

 

[Burrgogi]

Ah ha. That's great!! Thanks so much! Didn't know about that feature. Or maybe it's been so long I forgot since I've been working with Excel since it's inception.

I didn't know about it either until about a year ago I was working on a personal project of mine. I just kind of fumbled upon it by accident. Definitely comes in handy where you have data on several hundreds or thousands or rows.