Have written simple macro to clear contents of all cells in a particular
column. Works fine until some rows on the worksheet are hidden by
autofilter.
I would like to clear contents from all cells in a particular column, even
in rows hidden by autofilter.
Is this possible?

I think your choices are to show all the data, clear the range or to loop
through all the cells and clear each cell.



Philip Reece-Heal wrote:

Have written simple macro to clear contents of all cells in a particular
column. Works fine until some rows on the worksheet are hidden by
autofilter.
I would like to clear contents from all cells in a particular column, even
in rows hidden by autofilter.
Is this possible?

--

Dave Peterson

Thanks Dave

Looping seems to be the only way to go for the worksheet I am working on. I
have found that I have to give each cell address and clear it's contents,
then move onto next.
It's a slow method and another problem is defining the last used row in the
worksheet when the last row/rows are hidden by autofilter. I have tried all
sorts of tricks but can't crack it and have resorted to using an arbitary
quant in the for-next loop.

Any ideas on that?


"Dave Peterson" wrote in message
...
I think your choices are to show all the data, clear the range or to loop
through all the cells and clear each cell.



Philip Reece-Heal wrote:

Have written simple macro to clear contents of all cells in a particular
column. Works fine until some rows on the worksheet are hidden by
autofilter.
I would like to clear contents from all cells in a particular column,
even
in rows hidden by autofilter.
Is this possible?

--

Dave Peterson

dim wks as worksheet
dim myCell as range
dim myRng as range
set wks = worksheets("sheet9999")
with wks.autofilter.range.columns(1) 'what column should be cleared
'avoid the header row
set myrng = .resize(.rows.count-1,1).offset(1,0)
end with

for each mycell in myrng.cells
mycell.value = "" 'mycell.clearcontents ???
next mycell

(Untested, uncompiled. Watch for typos.)


Philip Reece-Heal wrote:

Thanks Dave

Looping seems to be the only way to go for the worksheet I am working on. I
have found that I have to give each cell address and clear it's contents,
then move onto next.
It's a slow method and another problem is defining the last used row in the
worksheet when the last row/rows are hidden by autofilter. I have tried all
sorts of tricks but can't crack it and have resorted to using an arbitary
quant in the for-next loop.

Any ideas on that?

"Dave Peterson" wrote in message
...
I think your choices are to show all the data, clear the range or to loop
through all the cells and clear each cell.



Philip Reece-Heal wrote:

Have written simple macro to clear contents of all cells in a particular
column. Works fine until some rows on the worksheet are hidden by
autofilter.
I would like to clear contents from all cells in a particular column,
even
in rows hidden by autofilter.
Is this possible?

--

Dave Peterson

--

Dave Peterson