View original (French)

Excel VBA: Copy Filtered Variable Range

Solved
Eaheru Posted messages 205 Status Member -  
 matjukid -
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

  1.
    pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
     
    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 --
    4
    1. matjukid
       
      Thank you for your good advice. That's really nice!
      0
  2. Eaheru Posted messages 205 Status Member 20
     
    Great, I'll integrate that :)
    Thank you!! :)
    1
  3. Eaheru Posted messages 205 Status Member 20
     
    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.
    1