Changing filtered cells

  • Thread starter Thread starter Charley Kyd
  • Start date Start date
C

Charley Kyd

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
 
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
 
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))
 
Range("YourColumnRange").ClearContents ' erases all cells, hidden or not
Range("YourColumnRange").SpecialCells(xlCellTypeVisible).Value = "X"
 
Tom,
Range("YourColumnRange").ClearContents ' erases all cells, hidden or not
Click to expand...

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
 
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.
 
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.
 
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
 
Back
Top