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!


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

--
Regards,
Tom Ogilvy


"Dean" wrote in message
...
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

"Dave Peterson" wrote in message
...
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 wrote:

No it's definitely unprotected.

"Dave Peterson" wrote in message
...
If that worksheet is protected, have your code unprotect the
worksheet, do
the
work and reprotect the worksheet.



Dean wrote:

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
"Dean" wrote in message
...
I think I may have just found the culprit - merged cells! Silly,
silly,
me.

"Dean" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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.


"Dean" wrote in message
...
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" wrote in message
...
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


"Dave Peterson" wrote in message
...
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


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



--

Dave Peterson















--

Dave Peterson

--

Dave Peterson