[RESOLVED] Replacing a filtered range

[Fonty]

I have a macro, which as the last step, has to take a column from a workbook and replace another in the active workbook. For this I simply use
WorkBooks(workboook1).WorkSheets(sheetx).Range("X1:X111").Value=WorkBooks(workboook2).WorkSheets(she etz).Range("Z1:Z111").Value

It worked perfectly. But I have just discovered that it doesn't work if I have initially filtered some value in the "sheetx". How can I replace a whole range even if part of this range is filtered?

[Siddharth Rout]

It worked perfectly. But I have just discovered that it doesn't work if I have initially filtered some value in the "sheetx". How can I replace a whole range even if part of this range is filtered?

The only way that i can think of is looping through all the cells in the columns and rows and doing what you are doing above

for example

Private Sub CommandButton1_Click()
 
startrange = 1
endrange = 15 'last cell of the row
startrange1 = 1
endrange1 = 6 'last cell of the column
 
For i = startrange To endrange
    For j = startrange1 To endrange1
    'sheets2-Destination sheet
    'sheets1-Source Sheet
 
    Sheets("sheet2").Cells(i, j).Value = Sheets("sheet1").Cells(i, j).Value
 
    Next j
Next i
 
End Sub

Hope this helps...

[malik641]

Found these two procedures on the help files. I modified it to remove the filter on sheetx and then restore it:

Option Explicit
 
Dim w As Worksheet
Dim filterArray()
Dim currentFiltRange As String
 
Public Sub RemoveFilters()
    Dim f As Long
    
    Set w = Worksheets("Sheet1")
    With w.AutoFilter
        currentFiltRange = .Range.Address
        With .Filters
            ReDim filterArray(1 To .Count, 1 To 3)
            For f = 1 To .Count
                With .Item(f)
                    If .On Then
                        filterArray(f, 1) = .Criteria1
                        If .Operator Then
                            filterArray(f, 2) = .Operator
                            filterArray(f, 3) = .Criteria2
                        End If
                    End If
                End With
            Next
        End With
    End With
    
    w.AutoFilterMode = False
'    w.Range("A1").AutoFilter field:=1, Criteria1:="S"
End Sub
 
Public Sub RestoreFilters()
    Dim col As Long
    
    Set w = Worksheets("Sheet1")
    w.AutoFilterMode = False
    For col = 1 To UBound(filterArray(), 1)
        If Not IsEmpty(filterArray(col, 1)) Then
            If filterArray(col, 2) Then
                w.Range(currentFiltRange).AutoFilter field:=col, _
                    Criteria1:=filterArray(col, 1), _
                        Operator:=filterArray(col, 2), _
                    Criteria2:=filterArray(col, 3)
            Else
                w.Range(currentFiltRange).AutoFilter field:=col, _
                    Criteria1:=filterArray(col, 1)
            End If
        End If
    Next
End Sub

I commented out what the help originally had (to find it, in the VBE type in "Autofilter Object" and go to that link).

Anyway you can use it like this:

Option Explicit
 
Public Sub TestMe()
    Dim wb1 As Excel.Workbook
    Dim wb2 As Excel.Workbook
    Dim rng1 As Excel.Range
    Dim rng2 As Excel.Range
    
    Set wb1 = Workbooks("Book1")
    Set wb2 = Workbooks("Book2")
    
    Set rng1 = wb1.Sheets("Sheet1").Range("A2:A10")
    Set rng2 = wb2.Sheets("Sheet1").Range("A2:A10")
    
    Call RemoveFilters
    
    rng1.Value = rng2.Value
    
    Call RestoreFilters
End Sub

As a small example. If your original filter doesn't match the filter you had before replacing the values, it will still filter to the original criteria without problems (it will be under "Custom..." in the Autofilter dropdown).

Hope this helps too!

[Fonty]

In fact, also regarding the posts I posted this week, I'm starting to think that somehow AutoFilter is causing a lot of bugs in my file. It seems that it suddenly disables Calculate after trying to run the macros with filters on.