Code reduction exercise

[Dryver14]

Hi, I was playing with a very small piece of code, essentially remove rows with X to new sheet and delete from Data sheet.

I am trying to see how far I can reduce the code, all three work and it is only a small learning exercise for me.

Is this as good as it gets or do you have some advice?

Sub MoveData()


    Dim WS1 As Worksheet
    Dim WS2 As Worksheet
    Dim LastRow As Long
    Dim NextRow As Long
    Dim i As Long
    
    Set WS1 = Worksheets("Data")
    Set WS2 = Worksheets("Report")
    
    LastRow = WS1.Cells(Rows.Count, 1).End(xlUp).Row
    NextRow = 2
    
    WS1.Range("A1:E1").Copy WS2.Range("A1")
    WS1.Select
        For i = LastRow To 2 Step -1
            If Cells(i, 2).Value = "Z" Then
                Cells(i, 1).Resize(1, 5).Copy WS2.Cells(NextRow, 1)
                NextRow = NextRow + 1
                Cells(i, 1).Resize(1, 5).EntireRow.Delete
            End If
        Next i
    
End Sub




Sub SmartMoveData()


Worksheets("Data").Range("A1").CurrentRegion.AutoFilter field:=2, Criteria1:="Z"
ActiveSheet.Cells(2, 1).CurrentRegion.Copy Worksheets("Report").Range("A1")
Application.DisplayAlerts = False
ActiveSheet.Range("A2", Range("A1").End(xlDown).End(xlToRight)).Delete
Application.DisplayAlerts = True
Worksheets("Data").Range("A1").CurrentRegion.AutoFilter


End Sub


Sub SmartMoveData2()


Application.DisplayAlerts = False


    With Worksheets("Data")
        .Range("A1").CurrentRegion.AutoFilter field:=2, Criteria1:="Z"
        .Cells(2, 1).CurrentRegion.Copy Worksheets("Report").Range("A1")
        .Range("A2", Range("A1").End(xlDown).End(xlToRight)).Delete
        .Range("A1").CurrentRegion.AutoFilter
    End With


Application.DisplayAlerts = True


End Sub

 

[Fazza]

Expanding on Peter's comments, with large numbers of ranges, sorting the data is often best. This groups all the rows with X together so there is only 1 single range to move/copy/delete - this is fast, whereas if there are large numbers (say 5,000 discontinuous ranges in a list of 60,000 rows) it can be really slow. Resort to original order if required.

 

[Dryver14]

Thanks, this is why I looked at the autofilter option.

Thanks for the comments, I can only improve through them.

In my real life I rarely come across data sets larger than a couple of hundred rows.