Help With my Macro

[Desu Nota from Columbus]

I am in the process of creating a macro that will eventually have many steps.

I currently have two steps:

1) Delete a bunch of columns and then shift everything left (works)

2) Delete all rows if they don't have "shift" in Column U (does not do anything) Why?

Sub StepwiseMacro()
    
    Range("A:A,C:C,E:E,F:F,H:H,I:I,K:K,L:L,M:M,N:N,O:O,P:P,Q:Q,R:R,S:S,T:T,W:W,AF:AF,AG:AG,AJ:AJ,AK:AK,AL:AL,AM:AM,AN:AN,AO:AO,AP:AP,AQ:AQ,AT:AT,AV:AV,AU:AU,AW:AW,AX:AX,AY:AY").Select
    Selection.Delete Shift:=xlToLeft
    Dim LR As Long
    Dim r As Long
    With Sheet1
        LR = .Range("U" & .Rows.Count).End(xlUp).Row
        For r = 1 To LR
            Select Case .Range("U" & r).Value
                Case Is = "Stub_Shift"
                    EntireRow.Delete Shift:=xlUp
                Case Is = "Job"
                    EntireRow.Delete Shift:=xlUp
                Case Is = "Stub_Job"
                    EntireRow.Delete Shift:=xlUp
            End Select
        Next r
    End With
End Sub

 

[shg]

Try this:

Sub StepwiseMacro()
    Dim lRow As Long
    Dim iRow As Long
 
    Range("A:A,C:C,E:F,H:I,K:T,W:W,AF:AG,AJ:AQ,AT:AT,AU:AW,AX:AY").Delete
 
    With Sheet1
        lRow = .Range("U" & .Rows.Count).End(xlUp).Row
        For iRow = lRow To 1 Step -1
            Select Case .Cells(iRow, "U").Value
                Case "Stub_Shift", "Job", "Stub_Job"
                    .Rows(iRow).Delete
            End Select
        Next iRow
    End With
End Sub

When you delete rows, you need to loop backwards (think about it).

I think you should qualify the range for the line that deletes all the columns.

 

[Desu Nota from Columbus]

Try this:

Sub StepwiseMacro()
    Dim lRow As Long
    Dim iRow As Long
 
    Range("A:A,C:C,E:F,H:I,K:T,W:W,AF:AG,AJ:AQ,AT:AT,AU:AW,AX:AY").Delete
 
    With Sheet1
        lRow = .Range("U" & .Rows.Count).End(xlUp).Row
        For iRow = lRow To 1 Step -1
            Select Case .Cells(iRow, "U").Value
                Case "Stub_Shift", "Job", "Stub_Job"
                    .Rows(iRow).Delete
            End Select
        Next iRow
    End With
End Sub

When you delete rows, you need to loop backwards (think about it).

I think you should qualify the range for the line that deletes all the columns.

This still did not delete the rows based on the criteria in U.

 

[shg]

Have you tried setting up a few rows of test data and stepping through the code?

http://www.cpearson.com/excel/debug.htm

 

[Desu Nota from Columbus]

I had to change the With Sheet1 to With Sheets("Sheet1")

Thank you