Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to get SUMPRODUCT on filtered cells | Excel Worksheet Functions | |||
Numbering cells that have been filtered | Excel Worksheet Functions | |||
Calculating with filtered cells | Excel Discussion (Misc queries) | |||
filtered cells | Excel Discussion (Misc queries) | |||
Filtered Cells | Excel Programming |