Filtering by list of keywords and pasting results into spreadsheet

**shg** · 08-22-2009, 02:34 PM

Add a row above and a column to the left of your data on sheet2.

Sub x()
    Dim cell       As Range
    Dim rData       As Range

    With Sheet2
        .AutoFilterMode = False
        Set rData = .Range("A1", .Cells(.Rows.Count, "H").End(xlUp))
    End With
    
    Sheet3.UsedRange.ClearContents
    Application.ScreenUpdating = False
    
    For Each cell In Me.Range("A1", Me.Cells(Me.Rows.Count, "A").End(xlUp))
        Sheet2.AutoFilterMode = False
        rData.Columns(1).Value = cell.Text
        rData.AutoFilter Field:=2, Criteria1:="*" & cell.Text & "*"
        rData.Offset(1).Copy Destination:=Sheet3.Cells(Rows.Count, "A").End(xlUp).Offset(1)
    Next cell
    
    Sheet2.AutoFilterMode = False
    rData.Columns(1).ClearContents
    Sheet3.Columns.AutoFit
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

**ej463** · 08-25-2009, 01:02 PM

Hello, So basically Im working with the macros you posted at 8:26 pm on 8/21 because that works great, the last one posted I encountered problems with, which is fine I can stick with the older one.

I have a question regarding the older macros because when I run it, my memory usage on task manager is at 300,000K for excel and then once I start the macros it increases every second by 1k until it reaches 1,700,000K and then the excel program freezes and it says it is out of memory even though I have 2gb of ram in my computer. Im not sure why the memory usage for this macros increases, shouldnt it remain constant because the same task is being repeated?

Also that macros works correctly, although slow, the output is correct, so I will use it. However, I dont want to keep restarting excel due to the memory usage error. I would like to just run the macros overnight and see the output in the morning. Thanks