Filter Loop

  • Looking for a simpler way to filter using a loop. Right now I have this code listed over and over again to filter for the numbers 6 to 238 but I believe this can be done in a simple loop function without listing it out 200+ times but I'm not sure how.

    Abbreviation rd refers to a delimited .txt document opened into a sheet called "Raw Data". I need to filter column B for each number 6 to 238 and copy the corresponding results from that filter that are in column C to a new sheet "Formatted Data", abbreviated fd, into the column corresponding to that filter number. I also need to add a piece that stops it from copying anything if the filter number is non existent in the column and move on to the next number.

    Example that I'm currently using:

    ''''' Filter: Mass 56
    rd.UsedRange.AutoFilter 2, "56"
    rd.Range("C2:C" & LastRow).Copy fd.Range("BD4")
    rd.AutoFilterMode = False

    ''''' Filter: Mass 57
    rd.UsedRange.AutoFilter 2, "57"
    rd.Range("C2:C" & LastRow).Copy fd.Range("BE4")
    rd.AutoFilterMode = False

    Any and all help is appreciated

  • Code Tags Added
    Your post does not comply with our Forum RULES. Use code tags around code.

    Posting code between


    tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window.

    (I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

  • I wouldn't use UsedRange, you need to define the filter range

    Why use VBA? You can AutoFilter for Numbers between the range manually.

    • Select any cell within the range.
    • Select Data > Filter.
    • Select the column header arrow .
    • Select Number Filters, and then select a comparison, like Between.
    • Enter the filter criteria and select OK.
  • Update: So this is what I have right now. The loop and filter check works but I need a way to use the integer I filtered for to be the column that the data pastes to if it's found. The amount of entries being copied&pasted is indefinite so I need the paste to always start in row 5 and column matching the IsoM and just continue down as necessary.

    This part specifically is what is not working. Please help.

    fd.Range(Cells(5, IsoM))

    *All set now. Changed it to fd.Cells(5, CIso) and now it is working

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!