mercredi 28 décembre 2016

Need to move rows in excel based off of contents in column but by listing what not to move in excel vba

I need to move rows based off of the contents from column 7. Now the thing is I need to list the things that are not to be moved over because they it's only 5 things ("YS,CO,CM,CS,SN") vs. a very long list that I do not even have a full list of all the possibilities.

I have thought that this could possibly be an if else statement or ifnot then statement but am unsure what the syntax would be.

Below I have what it would look like if I wanted to move the 5 items but I basically need the opposite.

Also I know the With .Range("$A$2:$O$" & Cells(Rows.Count, "A").End(xlUp).Row) part isn't correct so if someone happens to know how to make it go to the end of the page that would be a great help as well.

With Worksheets("YS,CO,CM,CS,SN")
    With .Range("$A$2:$O$" & Cells(Rows.Count, "A").End(xlUp).Row)
            .AutoFilter Field:=7, Criteria1:=Array("YS", "CO", "CM", "CS", "SN") _
            , Operator:=xlFilterValues
            .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible) _
            .Copy Destination:=Worksheets("Others").Range("A2")
            .SpecialCells(xlCellTypeVisible).Delete
        End With
        .AutoFilterMode = False
    End With

Aucun commentaire:

Enregistrer un commentaire