Excel VBA: Copy Filtered Variable Range

Solved
Hello,

I'm trying to solve the following problem since yesterday without success:
I have an Excel file that contains several hundred rows, and the operations are as follows:
- Filtering on column K,
- Copying the remaining rows after this filter to the first sheet of a new file.

My problem is that the range to copy is variable and changes every day, and when I attempt to copy/paste, I also end up transferring the rows that are normally filtered to the new file.
This is absolutely not what I want :)

Does anyone have an idea, please?

3 answers

  • Votes
  • Date
  • Descending date
  1. Moderator
    Hello,
    The syntax to copy only the visible rows is as follows:

    Sheets(SheetName).Range("A2:AZ16500").SpecialCells(xlVisible).Copy

    In bold: to be adapted

    for example:

    Range("A2:AZ16500") 
    can become "variable" like this:

    Range("A2:AZ" & Range("A65536").End(xlUp).Row)

    Best regards,
    -- There’s no such thing as a stupid question, only stupid answers. A.Einstein --
    1. Thank you for your good advice. That's really nice!
  2. So it works perfectly. And if in the meantime I want to delete the lines that I just copied from the source file (and from that source file)?

    Edit: it's all good, I'm doing it in two steps and everything works perfectly.
    Thanks again for the help.