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

 

[Dryver14]

Just noticed the last 2 do not work if the data sheet is not selected before use....Another line of code!

 

[RoryA]

Avoid selecting things - it is almost never required.

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")
    
   With WS1
    LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    NextRow = 2
    
    .Range("A1:E1").Copy WS2.Range("A1")

        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 With
End Sub

I'd also suggest turning off screenupdating and calculations while running the code.

 

[Dryver14]

Cheers Rory,

Are you saying the loop is better than the other 2 as no selection required, I get you point on the screen updating, I am only working with 60 lines of data for this exercise so didn't feel it was warranted

 

[RoryA]

No, the same principle would apply there, I'm just lazy.

Sub SmartMoveData()


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

End Sub

 

[Dryver14]

Thank Rory,

I seem to be on the right track so will keep plodding on.

 

[Peter_SSs]

I am trying to see how far I can reduce the code ..

Depends what you mean by "reduce the code". Do you mean
a) Have as few lines of code as possible?
b) Have the code do its job in the shortest possible time?

... do you have some advice?

I think often people focus on a) at the expense of b) but there are a number of considerations including

- The size (no. of rows) of the data. If the data is not very big, speed probably will not be an issue, but if it is very large then speed may be very important.
- What proportion of the rows and in how many distinct areas will likely need removal to the other sheet.
- Are there any formulas on the sheet or just constant values.
- If there are formulas, how many and what type.
- Who might need to maintain the code in the future and what their vba skills are like.
- etc

In the end, each case really needs to be judged on its merits in deciding the "best" code to use.

 

[Dryver14]

Thanks Peter,

I take your points on board...early stages, as I develop more complex procedures I can see each item needs to be addressed, as a side from this is there anywhere with large sheets of data that I can set myself projects on?

 

[Peter_SSs]

.. as a side from this is there anywhere with large sheets of data that I can set myself projects on?

I'm usually just trying to answer forum questions & mostly generate the data myself based on any sample data or information in the question.

 

[Dryver14]

I found some MikeGirvin, excelsfun has loads