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(xlCellType Blanks).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

You need to test for .Value = ""
Mike F
"Gunnar Johansson" wrote in message
...
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(xlCellType Blanks).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

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


"Mike Fogleman" skrev i meddelandet
news:I2JSc.134985$eM2.30210@attbi_s51...
You need to test for .Value = ""
Mike F
"Gunnar Johansson" wrote in message
...
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(xlCellType Blanks).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

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



Gunnar Johansson wrote:

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

"Mike Fogleman" skrev i meddelandet
news:I2JSc.134985$eM2.30210@attbi_s51...
You need to test for .Value = ""
Mike F
"Gunnar Johansson" wrote in message
...
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(xlCellType Blanks).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





--

Dave Peterson

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" skrev i meddelandet
...
How about applying Data|filter|autofilter and showing just the non-blanks.



Gunnar Johansson wrote:

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

"Mike Fogleman" skrev i meddelandet
news:I2JSc.134985$eM2.30210@attbi_s51...
You need to test for .Value = ""
Mike F
"Gunnar Johansson" wrote in message
...
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(xlCellType Blanks).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





--

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 wrote:

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" skrev i meddelandet
...
How about applying Data|filter|autofilter and showing just the non-blanks.



Gunnar Johansson wrote:

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

"Mike Fogleman" skrev i meddelandet
news:I2JSc.134985$eM2.30210@attbi_s51...
You need to test for .Value = ""
Mike F
"Gunnar Johansson" wrote in message
...
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(xlCellType Blanks).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





--

Dave Peterson


--

Dave Peterson

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


/Regards

"Dave Peterson" skrev i meddelandet
...
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 wrote:

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" skrev i meddelandet
...
How about applying Data|filter|autofilter and showing just the
non-blanks.



Gunnar Johansson wrote:

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

"Mike Fogleman" skrev i meddelandet
news:I2JSc.134985$eM2.30210@attbi_s51...
You need to test for .Value = ""
Mike F
"Gunnar Johansson" wrote in message
...
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(xlCellType Blanks).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





--

Dave Peterson


--

Dave Peterson