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.
Store a filtered range and write to sheet later
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Store a filtered range and write to sheet later
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Store a filtered range and write to sheet later
You could use this function:
Example of use:
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
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
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Store a filtered range and write to sheet later
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:
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
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.