Hide Columns

[Dean]

I tried to copy an existing macro I found to hide columns.

Sub hideColumns()
Application.Goto reference:="test.hide.column.by.DA"
Selection.SpecialCells(xlCellTypeFormulas, 16).Select
Selection.EntireColumn.Hidden = True
End Sub

The test.hide.column.by.DA is an entire row which has entries that are either "1" or #N/A. If it is the latter, that column should be hidden. The macro is bombing out and I can now guess why. I guess the way this macro works is that all N/A's are contiguous and once they start, they continue from left to right. In my example, any column may be N/A or 1. Can someone please re-work this macro so it will do them one column at a time?

Thanks so much!
Dean

 

[Dave Peterson]

Your code worked fine for me--as long as I had formulas (not just values) in
that range that evaluated to #n/a errors.

If you don't have formulas that evaluate to those errors, your code will not
work.

You can do this:

Option Explicit
Sub hideColumns()
Dim Rng as range

set rng = nothing
on error resume next
set rng = worksheets("somesheet").range("test.hide.column.by.da") _
.cells.specialcells(xlcelltypeformulas, xlerrors)
on error goto 0

if rng is nothing then
'what should happen
else
Application.Goto reference:="test.hide.column.by.DA"
rng.entirecolumn.hidden = true
end if
End Sub

 

[Dave Peterson]

Ps. This is a plain text newsgroup.

Dean wrote:

I tried to copy an existing macro I found to hide columns.

Sub hideColumns()
Application.Goto reference:="test.hide.column.by.DA"
Selection.SpecialCells(xlCellTypeFormulas, 16).Select
Selection.EntireColumn.Hidden = True
End Sub

The test.hide.column.by.DA is an entire row which has entries that are either
"1" or #N/A. If it is the latter, that column should be hidden. The macro is
bombing out and I can now guess why. I guess the way this macro works is that
all N/A's are contiguous and once they start, they continue from left to
right. In my example, any column may be N/A or 1. Can someone please re-work
this macro so it will do them one column at a time?

Thanks so much!
Dean

 

[Dean]

I think I was not clear. The macro that I plagiarized seems to work if
there is only one contiguous block of columns to be hidden. In my case, as
we go from left to right across a range of, say 100 columns, the entry in
the row being tested can randomly alternate between 1 and #N/A. So, in
fact, I might want to start at column C, leave it alone, then hide columns D
thru K, then leave column L and M alone, then hide column N, ...

My guess is that this plagiarized macro only works for finding out at which
column the NA's start and when they do, hiding all columns to the right. I
think that the macro I need, needs to do one column at a time.

Is that clearer?

Please give me an exact macro if you can, as I'm still a rookie.

Thanks, Dave!
Dean

 

[Dean]

I may have erred in this last post - please give me a minute. Could it be
that, if my formula has an or function inside an if statement, it won't
work?

I think I was not clear. The macro that I plagiarized seems to work if
there is only one contiguous block of columns to be hidden. In my case, as
we go from left to right across a range of, say 100 columns, the entry in
the row being tested can randomly alternate between 1 and #N/A. So, in
fact, I might want to start at column C, leave it alone, then hide columns
D thru K, then leave column L and M alone, then hide column N, ...

My guess is that this plagiarized macro only works for finding out at
which column the NA's start and when they do, hiding all columns to the
right. I think that the macro I need, needs to do one column at a time.

Is that clearer?

Please give me an exact macro if you can, as I'm still a rookie.

Thanks, Dave!
Dean

 

[Dean]

I doubt that my last suggestion is plausible but the macro does work with a
simpler formula but not with the or formula. It says it can't hide it. But
could it be something else?

Can you explain what the 16 in the code does? That might help me uncover
the problem.

I may have erred in this last post - please give me a minute. Could it be
that, if my formula has an or function inside an if statement, it won't
work?

 

[Dean]

It looks like the 16 is just a way to tell it to look for an #N/A, right?
Could an or function inside an if function cause it a problem? I know excel
has issues with some of its functions.

D

Unable to set the hidden property of the range class.

 

[Dean]

BTW, when the macro stops, I can see that all the cells with #N/As have been
highlighted. For some reason, it cannot seem to hide them.

 

[Dean]

I am no longer certain it is even working with a simpler formula now. Let
me ask, other than the worksheet being protected, what could cause it to not
be able to hide the columns ("Unable to set the hidden property of the range
class"). If it has highlighted alternating or random columns, is it really
possible to hide them all in one fell swoop, or does the macro somehow tell
it to loop from left to right and do them one column at a time, or blocks at
a time?

Thanks!
Dean

 

[Dean]

I think I may have just found the culprit - merged cells! Silly, silly, me.

 

[Dean]

Actually, I'm not sure that is causing the problem at all, since I can
manually highlight columns across or within the boundaries of merged cells
and it seems to work fine.

Any other ideas?

Thanks
Dean

 

[Dave Peterson]

If that worksheet is protected, have your code unprotect the worksheet, do the
work and reprotect the worksheet.

 

[Dean]

No it's definitely unprotected.

If that worksheet is protected, have your code unprotect the worksheet, do
the
work and reprotect the worksheet.

 

[Tom Ogilvy]

If the formula produces an error value then the code should work

=if(condition,number,na())

if you have it display the string
"#N/A"

as in
=if(condition,number,"#N/A")
then that probably won't work.


yes, the second argument of 16 says to look for formulas that produce error
values.

From the immediate window:

? xlErrors
16

Regards,
Tom Ogilvy

 

[Dave Peterson]

Could be merged cells. I know that each version of excel has gotten better with
the way it handles merged cells.

What version of excel are you using?

I tested in xl2003 and your original code worked ok for me (as did the suggested
code).

 

[Dean]

Excel 2002 - here's clue - I got some other pattern to work fine, so I was
suspicious. So, I went back to the problematic pattern and tried to
manually hide each block of columns with N/As. As Murphy would have it,
only near the very end, when I tried to do so, did I find a problem, one
which I assume is related. I got a message that said "cannot shift objects
off sheet.".

Yes, I am using all but two columns but the last two columns are absolutely
blank., And anyway, why should hiding columns make it worse. What could
this object be?

Thx,
Dean

 

[Tom Ogilvy]

http://support.microsoft.com/kb/211769/en-us
"Cannot shift objects off sheet" error message when you hide columns in
Excel

--
Regards,
Tom Ogilvy

Excel 2002 - here's clue - I got some other pattern to work fine, so I was
suspicious. So, I went back to the problematic pattern and tried to
manually hide each block of columns with N/As. As Murphy would have it,
only near the very end, when I tried to do so, did I find a problem, one
which I assume is related. I got a message that said "cannot shift
objects off sheet.".

Yes, I am using all but two columns but the last two columns are
absolutely blank., And anyway, why should hiding columns make it worse.
What could this object be?

Thx,
Dean

 

[Dean]

Thanks so much, Tom. Who would have thought some measly cell comments could
cause this problem. Things seem fine now. I just hope I can remember to
not insert some cell comments later. I do macro programming about 1% of my
work time, if that, and it seems like I keep running into bugs like these,
almost each time. I don't know how folks like you stand it. I guess you
just have this mental library of all the quirks - impressive!

Dean

http://support.microsoft.com/kb/211769/en-us
"Cannot shift objects off sheet" error message when you hide columns in
Excel

 

[Dean]

This is my 2nd thank you post because the one I posted last night doesn't
seem to be anywhere!! I have to (attempt to) create macros infrequently
and I seem to run up against these all too often. I guess you have to do
macros often to be aware of all these bugs!!

That did the trick, Tom! I thank very much!

http://support.microsoft.com/kb/211769/en-us
"Cannot shift objects off sheet" error message when you hide columns in
Excel