Clear contents of cells hidden by autofilter

[Philip Reece-Heal]

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]

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]

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]

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.)