Store a filtered range and write to sheet later

[Rudi]

Hi,

I have a single column of values under a heading called Codes.
If I filter this single column based on a condition, I'd like to store this filtered result in a variable.
Once I have the result (stored in a variable) I want to delete the original list (Columns("A:A").Clear)
Then put the filtered result back on the page in the place where the original list was.

Is this possible with some sort of array variable?

(I know I can copy the list into a new temporary sheet, then delete the original data and paste the temp sheet data back, but my question is more as a learning curve with arrays).
TX.

[HansV]

You could use this function:

Code: Select all

Function GetFiltered(rng As Range)
    Dim arr() As Variant
    Dim n As Long
    Dim cel As Range
    For Each cel In rng.SpecialCells(xlCellTypeVisible)
        n = n + 1
        ReDim Preserve arr(1 To n)
        arr(n) = cel.Value
    Next cel
    GetFiltered = arr
End Function

Example of use:

Code: Select all

Sub Testing123()
    Dim arr As Variant
    arr = GetFiltered(Range("A1:A199"))
    ActiveSheet.ShowAllData
    Range("A1:A199").ClearContents
    Range("A1").Resize(UBound(arr)).Value = Application.Transpose(arr)
End Sub

[Rudi]

TX. That process works well.
It seems to be a bit more complex due to the filtered range (which this code you posted overcomes).

I found a code structure that is easier to set up but it would only work for straight forward range references and not through filtered or noncontiguous ranges.

Code:

Code: Select all

Dim Src As Variant
   Src= Sheets("Src").Range("A2:A9").Value 'Read range to array
   'Here you can add code to manipulate your Src array
   '...
   Sheets("Dest").Range("A2").Value = Src 'Write array back to another range