Macro to hide rows/colums by value

[eirens]

HIDING
I'd like to be able to hide various rows and columns that have the
number 1 in their first position.

For example:

If A4 =1 then hide row 4.

If B6=1 then hide column B.

I'm okay with never being alble to hide row 1 or column A so there
shouldn't be any conflict/ambiguity.

UNHIDING
I'm happy to just unhide all when I need to unhide any. How can I do
that?

Would someone point me in the right direction?

Thanks for any help.

/e

 

[Guest]

why a row for A4 and a column for B6?

What are your rules,

What cells should be checked.

Is this something you would do by running a macro, or do you want them to
magically hide when you enter the 1 - or is the 1 produced by calculation.

Help yourself by providing a complete explanation of what you want to do.

sub RunMe()
With Range("A4")
.EntireRow.Hidden = ( .Value = 1)
end with
With Range("B6")
.entirecolumn.Hidden = (.Value = 1)
End With
End sub

 

[eirens]

Tom,

A4 and B6 were examples.

I'd like to be able to define the rows rows and columns to hide by
manually placing a value of 1 in cells around the perimeter of the
sheet.

I do not need execution of the hide and unhide macros hooked to any
events; I'll run them manually.

That is:

Columns: For each column I want to hide, I'll place a 1 in row 1 at
the corresponding column.

Rows: For each row I want to hide, I'll place a 1 in column A on the
corresponding row.

But the question arises: Should we hide a row or a column if user
placed 1 in A1? So I originally said I'd be happy to ignore A1. But I
now see that a 1 in A1 should hide both row 1 AND column A. That'd be
perfect as I don't need to see those manually placed 1 values except
after an "unhide all" operation..

Is that fully clear?

Thanks.

/e

 

[eliano]

Hi friends.
Try:

Sub RunMe()
With Range("A1")
..EntireRow.Hidden = (.Value = "a")
'End With
'With Range("A1")
..EntireColumn.Hidden = (.Value = "A")
End With
End Sub

Regards,
Eliano


(e-mail address removed) ha scritto:

 

[eliano]

Sorry.

Manually ?
..EntireRow.Hidden = (.Value = "R")
..EntireColumn.Hidden = (.Value = "C")

Eliano

eliano ha scritto:

 

[eirens]

Eliano,

Thanks, but is there a way to adapt your solution to every row and
column (or perhaps a range of 20 rows and columns) instead of A1
explicitly -- so I don't have to write multiple versions of that macro
for every column or row?

/e

 

[Tom Ogilvy]

Dim Cols as Range, Rws as Range
Dim cell as Range
Rows.Hidden = False
Columns.Hidden = False
On Error Resume Next
set Cols = rows(1).SpecialCells(xlConstants,xlNumbers)
set Rws = columns(1).SpecialCells(xlConstants,xlNumbers)
On Error goto 0
if not Cols is nothing then
for each cell in cols
if cell.Value = 1 then
cell.EntireColumn.Hidden = True
end if
Next
End if
if not Rws is nothing then
for each cell in Rws
if cell.Value = 1 then
cell.EntireRow.Hidden = True
end if
next
End if

 

[eirens]

Thank you very much. That works exactly as I described.

/e