Conditional hiding of entire rows
 

Hi All

I was wondering if anybody knew how to hide an entire row or column based on
a stated condition using a specific cell elsewhere on the worksheet. I know
that it is possible to use [Format] Conditional Formatting to make the cell
appear empty, but I want to hide the entire row or column so that it no
longer appears on the worksheet. Is there a way to do this with or without
using VBA?

Thanks in advance and have a happy new year.

Nick

Conditional hiding of entire rows
 

You could use autofilter to filter that condition, but that would not be
dynamic. Other than that VBA looks the best bet.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Nick Turner" wrote in message
...
Hi All

I was wondering if anybody knew how to hide an entire row or column based
on
a stated condition using a specific cell elsewhere on the worksheet. I
know
that it is possible to use [Format] Conditional Formatting to make the
cell
appear empty, but I want to hide the entire row or column so that it no
longer appears on the worksheet. Is there a way to do this with or
without
using VBA?

Thanks in advance and have a happy new year.

Nick

Conditional hiding of entire rows
 

Thanks Bob

Yes, I thought VBA would be the best option as I need it to be dynamic. Any
Ideas on code to use? Do you know of any similar examples that I could view?

Cheers

Nick

"Bob Phillips" wrote:

You could use autofilter to filter that condition, but that would not be
dynamic. Other than that VBA looks the best bet.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Nick Turner" wrote in message
...
Hi All

I was wondering if anybody knew how to hide an entire row or column based
on
a stated condition using a specific cell elsewhere on the worksheet. I
know
that it is possible to use [Format] Conditional Formatting to make the
cell
appear empty, but I want to hide the entire row or column so that it no
longer appears on the worksheet. Is there a way to do this with or
without
using VBA?

Thanks in advance and have a happy new year.

Nick

Conditional hiding of entire rows
 

How would you want to identify which row(s) gets hidden when this cell
becomes a certain value?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Nick Turner" wrote in message
...
Thanks Bob

Yes, I thought VBA would be the best option as I need it to be dynamic.
Any
Ideas on code to use? Do you know of any similar examples that I could
view?

Cheers

Nick

"Bob Phillips" wrote:

You could use autofilter to filter that condition, but that would not be
dynamic. Other than that VBA looks the best bet.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Nick Turner" wrote in message
...
Hi All

I was wondering if anybody knew how to hide an entire row or column
based
on
a stated condition using a specific cell elsewhere on the worksheet.
I
know
that it is possible to use [Format] Conditional Formatting to make the
cell
appear empty, but I want to hide the entire row or column so that it
no
longer appears on the worksheet. Is there a way to do this with or
without
using VBA?

Thanks in advance and have a happy new year.

Nick

Conditional hiding of entire rows
 

Hi Bob

Thanks for the quick reply. The criteria I would use would be embedded
within formulas within Excel formulas. The output of these formulas would be
two cells which have a number representing the start row and end row for
hiding. I have mocked up some VBA code which seems to pass my preliminary
tests. Any suggestions or comments on my code? I'm a bit new to the VBA
side of Excel.

Sub HideEmptyRows()
Rows("23:35").EntireRow.Hidden = False
With Worksheets("A sent types")
firstrow = .Range("B40")
lastrow = .Range("B41")
For i = firstrow To lastrow
Rows(i).EntireRow.Hidden = True
Next i
End With
End Sub

I wanted to hide these rows in a dynamic manner because I am creating charts
with varying numbers of series. Now, whenever the entire row is hidden, it
is excluded from my chart, which is what I wanted. If you know of other ways
of excluding series from charts in a dynamic manner, that too would be
useful.

Thanks again. I have found your other posts on this site to me most useful
over a long period of time.

Nick


"Bob Phillips" wrote:

How would you want to identify which row(s) gets hidden when this cell
becomes a certain value?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Nick Turner" wrote in message
...
Thanks Bob

Yes, I thought VBA would be the best option as I need it to be dynamic.
Any
Ideas on code to use? Do you know of any similar examples that I could
view?

Cheers

Nick

"Bob Phillips" wrote:

You could use autofilter to filter that condition, but that would not be
dynamic. Other than that VBA looks the best bet.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Nick Turner" wrote in message
...
Hi All

I was wondering if anybody knew how to hide an entire row or column
based
on
a stated condition using a specific cell elsewhere on the worksheet.
I
know
that it is possible to use [Format] Conditional Formatting to make the
cell
appear empty, but I want to hide the entire row or column so that it
no
longer appears on the worksheet. Is there a way to do this with or
without
using VBA?

Thanks in advance and have a happy new year.

Nick