Changing filtered cells
 

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

Changing filtered cells
 

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

Changing filtered cells
 

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

Changing filtered cells
 

If the filtered range is a simple filter where the returned list consists of
one unique value, then ColumnDifferences will return all hidden cells

Dim rnghidden As Range
Set rnghidden = Range("filterlist").ColumnDifferences( _
Range("filterlist").SpecialCells(xlCellTypeVisible )(1, 1))


"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

Changing filtered cells
 

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

Changing filtered cells
 

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

Changing filtered cells
 

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.

ActiveCell.EntireColumn.Clearcontents

works.

Ask a specific question, get a specific answer.

--
Regards,
Tom Ogilvy



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

Changing filtered cells
 

Let me be clearer with what my thinking is

in a logical sequence of events

filter applied, but no criteria applied,

clear the column

Apply the criteria

Mark your cells using special cells.

set rng = Activesheet.Autofilter.Range.columns(1)
rng.offset(1,0).Resize(rng.rows.count-1).clearcontents


If that doesn't fit your scenario, then you will have to deal with the
limitations you have described.

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

was incorrect advice.

--
Regards,
Tom Ogilvy




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

ActiveCell.EntireColumn.Clearcontents

works.

Ask a specific question, get a specific answer.

--
Regards,
Tom Ogilvy



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

Changing filtered cells
 

Tom,

I've tested ActiveCell.EntireColumn.ClearContents against AutoFiltered data
in Excel 9, 10, and 11. It clears data in visible rows, but ignores data in
filtered cells. It does, however, clear data in manually hidden rows in all
three versions.

If you want the spreadsheet I used for these tests, write me privately.

All the best,

Charley



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

ActiveCell.EntireColumn.Clearcontents

works.

Ask a specific question, get a specific answer.

--
Regards,
Tom Ogilvy



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

Changing filtered cells
 

Agreed - see my follow up post.

I stated that I was incorrect in my original advice.

--
Regards,
Tom Ogilvy

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

I've tested ActiveCell.EntireColumn.ClearContents against AutoFiltered
data
in Excel 9, 10, and 11. It clears data in visible rows, but ignores data
in
filtered cells. It does, however, clear data in manually hidden rows in
all
three versions.

If you want the spreadsheet I used for these tests, write me privately.

All the best,

Charley



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

ActiveCell.EntireColumn.Clearcontents

works.

Ask a specific question, get a specific answer.

--
Regards,
Tom Ogilvy



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