Hi VBA geniuses
I have a table with data that i am filtering with the (=FILTER) function.
Each time I have applied a filter ("A4 & "A6") I would like to save that range in a Global Array, and change the filter accordingly with a different setting for a new range to save.
Once I am done with chosing filters and populating the Array, I would like to return the whole thing (combinedRanges) in another output range.
I have written the following code, but it is not returning the desired outcome. I might have made it too complicated, however, im looking for any help you can give me.
Column "E:I" and "K:M" is my source range
Column "AC:Aj" is my destination range
CODE:
Sub SaveRange()
Dim lastRow As Long
Dim combinedRange As Range
lastRow = Range("E" & Rows.Count).End(xlUp).Row ' Find the last used row in column T
' Define the ranges to save
Set combinedRange = Union(Range("E2:I" & lastRow), Range("K2:M" & lastRow))
' Check if the last used row is within the range
If lastRow <= combinedRange.Row + combinedRange.Rows.Count - 1 Then
currentIndex = currentIndex + 1
ReDim Preserve savedRanges(1 To currentIndex)
Set savedRanges(currentIndex) = combinedRange
End If
End If
End Sub
Sub ReturnRanges()
Dim i As Integer, j As Integer
Dim outputRange1 As Range, outputRange2 As Range
Set outputRange1 = Range("AC4:AG4").Resize(UBound(savedRanges), 5)
Set outputRange2 = Range("AH4:AJ4").Resize(UBound(savedRanges), 3)
' Loop through the savedRanges array and copy each range to the appropriate output range
For i = 1 To UBound(savedRanges)
For j = 1 To 5
outputRange1(i, j).Value = savedRanges(i).Cells(1, j + 19).Value
Next j
outputRange2(i, 1).Value = savedRanges(i).Cells(1, 27).Value
outputRange2(i, 2).Value = savedRanges(i).Cells(1, 28).Value
Next i
Erase savedRanges ' Clear the savedRanges array
currentIndex = 0 ' Reset the currentIndex variable
End Sub
Bookmarks