Conditional hiding of entire rows

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

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

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)

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

 

[Bob Phillips]

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)

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

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)

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

 

[Guest]

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

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)

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

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)


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