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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >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