How can I hide rows and retain row numbers?

[Mayweed]

I'm using the code below to hide blank rows but I'm also hiding the row numbers.
Is there a way to hide the rows and retain the row numbers, counting from the bottom up perhaps?
Any advice is much appreciated.

Please Login or Register  to view this content.

[Roger Govier]

Hi

You could always insert a new column A and have the formula
=SUBTOTAL(103,$B$2:B2)
copied down the the range

You could do this y as part of your existing code.

[Mayweed]

Roger,
Thank you for the reply but I can't see how that can work, I've attached a workbook if you have time to demonstrate your technique but I would prefer to have the rows hidden with VB if possible.

[royUK]

Do you mean the grey numbers at the side? If you do then hiding rows will hide these numbers, no way to avoid it

[Mayweed]

Hi Roy,
Yes I meant the grey numbers, thanks for the answer.
The code seems to be slow with screen flicker for some reason, if I use delete rows it removes the screen flicker but takes longer to execute.
The code I'm using was originally written in part by you in reply to one of my posts some time ago, would you mind looking over it to see if there is any way to streamline it at all?

[abousetta]

Have you tried to turn off some of excel's functionality, if screen flicker is part of the problem:

Please Login or Register  to view this content.

abousetta

[Mayweed]

Hi Abousetta,
Thank you for the code, I was only using 'ScreenUpdating false' but even your code dosen't stop the flicker or speed problem.
Perhaps I'm trying to hide too many rows at once, I've decided to cut the rows down to 500 and delete them; it would seem that deleting rather than hiding, stops the screen flicker.
Thanks again.

[Roger Govier]

Hi

I thought you just wanted to show a row number for each visible row.
If you want to Not Show the actual row numbers, then you would have to Hide headings.

The attached workbook shows you how.

[Leith Ross]

Hello Mayweed,

I don't experience the same problem on my system, but there are a number of factors that contribute to screen flicker. One of which is switching between tasks. When VBA executes it tends to "hog'" the system. Using DoEvents can sometimes smooth out the process. I have added that to your here. Give it a try, it may help.

Please Login or Register  to view this content.

[Mayweed]

Hi Leith,
Thanks for the code, it works! I scattered a few 'DoEvents' here and there and the screen flicker has disappeared.

[royUK]

This will toggle visibility

Please Login or Register  to view this content.

[Mayweed]

Roy, that helps a lot with my 'hide rows/retain numbers' problem, thank you.

[royUK]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save