Help to hide rows, SpecialCells(xlCellTypeBlanks) doesn't work...

[Gunnar Johansson]

Hi,
I try to hide all rows exept them with A:A cells with values. I have
formulas in the cells and if the formula give "" the row should be hidden.
The formulas are references to other cells in other sheets, like "
='Sheet2'!B10 " and the reference cells are also formulas, if that matter.

I have tried with
Blad102.Range("A737:A835").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden =
True

It doesn't work because xlCellTypeBlanks consider formulas to be "not
blanks", I guess.

The 'Sheet2'!B10 and similar cells will contain either text or ""
If 'Sheet2'!B10 gives value "" the A:A cell should be hidden, else it
shuold be visible

In some A:A cells I have manually using a few space strikes to be ensured
the rows always should be visible.

Any suggestions?

/Regards

 

[Mike Fogleman]

You need to test for .Value = ""
Mike F

 

[Gunnar Johansson]

Thank you for the answer.

But then I need a For Each Next Loop and I already have one like that
running. I would need a quicker one like a "SpecialcCell" operation. Aren't
there any other possibilities?

/Regards

 

[Dave Peterson]

How about applying Data|filter|autofilter and showing just the non-blanks.

 

[Gunnar Johansson]

I thought of that...maybe. But the rows are used in a report and I don't
know if the tabel is looking good with the big fat "arrowbuttons" hanging on
each headline. Actully, one of the headlines of one of the smaller columns
is compleatly hidden.

Are there a way to hide the arrow buttons at the headline???

And it is three different tables that should be filtered in the same
worksheet, with text between, is this possible? I belive it's only one
filter function in a sheet, or? The information is liked to three different
sheets, so I might do the filering there - but I'm not sure how I can paste
it /link it / code it to the report when it is a variable amount of rows in
the three tables and the filtered tables had to follow each other without
any empty rows etc, just a number of rows with text between...

Suggestions?


/Regards

 

[Dave Peterson]

You're right--one filter (many columns, though) per worksheet (at a time!).

You can hide the dropdown buttons in code. Debra Dalgleish has a sample at:
http://www.contextures.com/xlautofilter03.html#Hide

She has lots of sample code to work with filtered data on that page.

You could apply the filter, filter the data, copy the visible cells, paste to
new location.

and do it twice more.

 

[Gunnar Johansson]

Thank you, it's running fine now, by autofilering in other sheets and using
PasteSpecial (xlPasteValues) to the new location.


/Regards