Unhide in a macro

[Anon]

I have a worksheet that I am trying to automate.

In this worksheet (many files I have to convert to add calculations)
Columns N are hidden all the way to the right
Rows 112 and on down are hidden.

I need to open them up all the way to put statistical data in there.

I tried to run the recorder, but it does not select M:N to unhide

example:
Columns("M:N").Select
Selection.EntireColumn.Hidden = False
ActiveWindow.SmallScroll Down:=102
Rows("110:116").Select
Selection.EntireRow.Hidden = False
ActiveWindow.SmallScroll Down:=-99
ActiveWindow.SmallScroll ToRight:=11
Range("S14:V22").Select
Selection.Cut Destination:=Range("Y14:AB22")
Range("M12").Select



Is there a way to globally unhide rows, columns in a macro?

Thanks
Paul

 

[Héctor Miguel]

hi, Paul !

I have a worksheet that I am trying to automate.
In this worksheet (many files I have to convert to add calculations)
Columns N are hidden all the way to the right
Rows 112 and on down are hidden.
I need to open them up all the way to put statistical data in there.
I tried to run the recorder, but it does not select M:N to unhide
(...)
Is there a way to globally unhide rows, columns in a macro?

do you mean something like this ?...

With Intersect(Columns("m:n"), Rows("110:116"))
.EntireColumn.Hidden = False ' or True or Not .EntireColumn.Hidden
.EntireRow.Hidden = False ' or True or Not .EntireRow.Hidden
End With

hth,
hector.

__ code __

 

[Anon]

Works like a charm!
The "record" function never seems to work now with progressive Excel version
updates ......<G>

Thanks again!
Paul

 

[Anon]

it works for 4 rows, but.....
It stops working if I try to open up more rows- like to row 200... Any
ideas?

Thanks

 

[Anon]

A little more definition after troubleshooting:

opening up to columns M:Z works if I leave it at rows 110:116
changing rows 110:116 to something like 196 or 200, gives me an error
message, but 156 works... I will have to look at why the spreadsheet seems
to be corrupt with a higher number..... luckily I can get by with 150,
which does work. This is an old template- probably dating back to MAC days,
so there could be some collapsed rows in addition to hidden rows... etc.
Bottom line- I can make it work by keeping the unhide below 150 rows

Thanks

 

[Héctor Miguel]

hi, Paul !

*corrupted* file is a (non desirable) possibility :-((

this code has working just ok...

With Intersect(Columns("n:az"), Rows("10:516"))
.EntireColumn.Hidden = Not .EntireColumn.Hidden
.EntireRow.Hidden = Not .EntireRow.Hidden
End With

hth,
hector.

__ OP __

 

[Anon]

yes, when the macro bombs- excel locks up.
I think we need to recreate the sheet from here forward and ditch the
corrupted one- after we calculate all the historical stats.

Thanks