Tom,

Range("YourColumnRange").ClearContents ' erases all cells, hidden or not

My tests show that the answer isn't quite that simple. Consider this
dataset, which I entered with "Title" in cell A1 of a spreadsheet:

Title Data
a 2
b 3
b 4
b 5
c 6
c 7

Name the column of numbers Data. Set AutoFilter. Select the cells that
contain the values 2 and 6 (cells B2 and B6). Name this two-cell selection
Test1. Using the dropdown in the Title column, choose "b". Now run this
macro:

Sub Foo1()
Dim rngData As Range
Set rngData = ThisWorkbook.Names("Test1").RefersToRange
rngData.ClearContents
End Sub

When you show (All) with the Title filter, the macro has worked as expected.
The 1 and 5 have been erased.

But let's try another test. Re-enter the missing numbers. And this time,
choose cells B2, B4, and B6, and name this three-cell selection Test2. Then
choose "b" again in the Title filter. Change the macro to look at Test2
data, rather than Test1. Then run the macro.

When you show all the data you'll see a different result. The unhidden cell,
cell B4, has been erased--as expected. But the two hidden cells have *not*
been erased.

So, rngData.ClearContents only works on AutoFilter-hidden cells if all cells
in rngData have been hidden. If any cell in rngData is visible, only the
visible cells are erased.


Unfortunately, that's not the end to the strangeness. Enter this macro:

Sub Foo2()
Dim rngData As Range
Set rngData = ThisWorkbook.Names("Data").RefersToRange
rngData = ""
End Sub

Select b in the Title's AutoFilter, and then run the macro. When you select
All, you'll see that only the unfiltered cells were overwritten with a null
string. That makes some sort of sense. So, let's try another test.

Discontiguously select cells B2 through B7. That is, select cell B2, hold
down Ctrl, click on cell B3, then click on cell B4, and so on, with Ctrl
held down in all cases. Name this range of 6 separate cells Test3. In Foo2,
change "Data" to "Test3". Then select b in the Title's AutoFilter, and run
the macro.

When you choose All in the Title column, you'll see that all Data has been
overwritten with null strings. If you keep playing around with various Test
ranges, you'll eventually discover what's happening.

When we use a statement like...
rngData = sSomeString
....only visible areas will be affected if any Area in rngData includes both
filtered and unfiltered cells. If separate Areas are exclusively filtered or
unfiltered, then rngData = sSomeString works like a champ.

Regards,

Charley Kyd


"Tom Ogilvy" wrote in message
...
Range("YourColumnRange").ClearContents ' erases all cells, hidden or not
Range("YourColumnRange").SpecialCells(xlCellTypeVi sible).Value = "X"

--
Regards,
Tom Ogilvy

"Charley Kyd" wrote in message
...
David,

I'll use SpecialCells(xlCellTypeVisible) to mark the visible area.
That's
not the problem. But how do I initialize the entire column--particularly
the
filtered areas? Somehow, I need to ensure that the hidden areas are
empty
or have different content than the unhidden areas, so that part of a
DSUM
criteria can identify only the visible areas and thus allow additional
criteria to return the sums of subsets of the visible data.

I suppose I could unhide everything, erase the entire column, then
re-establish each filtered column. But that's a lot of work to write and
takes a long time to execute.

The bottom line: How **without looping** can I write data to a range of
cells, some of which are hidden by AutoFilter?

Thanks.

Charley



"David Hager" wrote in message
...
Try:

Range("YourColumnRange").SpecialCells(xlCellTypeVi sible).Value = "X"

--
David Hager
Excel MVP


"Charley Kyd" wrote in message
...
I have a database with many AutoFiltered columns. I'm writing a Sub
that
will put an "X" in all visible cells in a specific column. To do so,
I
first
need to erase or mark *all* cells in that record-keeping column so I
then
can mark only the visible cells.

These methods fail:

rngFull.ClearContents
rngFull = ""
rngCell.Copy rngFull

Can anyone suggest a way to erase filtered cells, **without
looping**
and
without unhiding the AutoFiltering?

Thanks.

Charley