Working with cells hidden by filtering

[kingd123]

Could anyone let me know how to copy a column, or blank a column,
including any rows hidden by filtering or AutoFilter ?

For example, if I have a worksheet with rows 1-10,
and AutoFilter is only showing rows 4-6:



Dim Data As Range

Set Data = ActiveSheet


' Copying column
' ==========

Data.Columns(1).Value = Data.Columns(2).Value

' Source contains all 10 rows of column 2

' However, on setting destination, effectively does following,
' which is not what I want at all

' cell(4,1) = cell(1,2)
' cell(5,1) = cell(2,2)
' cell(6,1) = cell(3,2)

' If AutoFilter is off, everything works fine:

' cell(1,1) = cell(1,2)
' cell(2,1) = cell(2,2)
' cell(3,1) = cell(3,2)
' cell(4,1) = cell(4,2)
' cell(5,1) = cell(5,2)
' cell(6,1) = cell(6,2)
' cell(7,1) = cell(7,2)
' cell(8,1) = cell(8,2)
' cell(9,1) = cell(9,2)
' cell(10,1) = cell(10,2)


' Blanking column
' ==========

Data.Columns(3).Value = ""

' Only blanks following:

' cell(4,3)
' cell(5,3)
' cell(6,3)

' I want to blank all 10 cells in column 3


I was wondering if I could do the following:

1) Save filter / AutoFilter status for worksheet
2) ShowAllData
3) Do my column copy, or column blank
4) Restore filter / AutoFilter status for worksheet


I also looked at SpecialCells(xlCellTypeAllFormatConditions).
But that appears to only return cells with conditional formatting,
not all cells, including cells hidden by filtering.


I also wondered if I should create a CustomView, with no filtering.
But I think worksheet manipulation only acts on the 'main' view.

 

[Damon Ostrander]

Hi kingd123,

I suggest you just do it with a loop, like this:

Dim iRow As Long
iRow = 2
Do Until IsEmpty(Cells(iRow, "B"))
Cells(iRow, "A") = Cells(iRow, "B")
iRow = iRow + 1
Loop


Damon