Keep cells hidden when using autofilter

[NicolaasH]

Hi,

I have an Excel workbook in which I use an autofilter.

Some of the rows in the list always have to remain hidden. However, when I hide a row, or give it a height of 0, these rows reappear again if I use the Autofilter (All) selection or Filter-> Show All
I also use VBA code to show all data, which also makes the hidden rows reappear.
Is there a way of removing an autofilter without unhiding the selected rows? Can I somehow fully block the use of these rows?

Thanks!
Nick

 

[SkipVought]

Nick,

Why are these row always hidden?

How do they relate to the data that is not hidden?

Please be clear, concise and complete.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[NicolaasH]

The first row in the List is hidden because it has default information that I copy to new rows (using VBA).

I realise that I could have placed this information somewhere else, but changing this now is a lot of work (code adaptation). Unfortunately I only just found out that these rows become unhidden. Therefore I was wondering whether there was an easy way of solving this where I can leave everything as it is and just keep the row hidden.

 

[SkipVought]

"The first row in the List is hidden because it has default information that I copy to new rows (using VBA)."

1. Keep it somewhere else or
2. change the font color to make it appear as an empty row.

Don't know why it a big deal to change your VBA to keep the row of data on a hidden sheet, for instance. Name the range. The copy and paste is a nit!.




Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[NicolaasH]

Skip, I guess your anwer is: bad luck, there is no solution?

The big deal is that I estimate it to be at least 3 hours work to change everything in different workbooks, worksheets and a whole lot of VBA procedures, while at the same time I thought I had finished my work....

thanks anyway
Nick

 

[SkipVought]

"The big deal is that I estimate it to be at least 3 hours work to change everything in different workbooks, worksheets and a whole lot of VBA procedures..."

I'd think about what you are doing, replicating the same data in scores of workbooks. You might, in the future, consider a SINGLE SOURCE for data like this, that would entale less maintenance effort when changes occur.

Just a thought. Good luck!

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[Spooku777]

One possible solution is to put your hidden row above the filter row - that is, above the row with the filters dropdowns.

I'm assuming you have a header row in Row 1 of the sheet. So, if you unhide your Default Info row, cut it and insert paste it to Row 1 (then hide it again) all the filtering should occur below it and it should remain hidden.

AND, you shouldn't have to change your VBA code since (I'm assuming again) that your it ignores the first 2 rows anyway.