Help With my Macro

D

Desu Nota from Columbus

Well-known Member
Joined
Mar 17, 2011
Messages
556
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?


Code: 
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Try this:

Code: 
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.
 
Upvote 0
shg said:
Try this:

Code: 
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.
Click to expand...


This still did not delete the rows based on the criteria in U.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Macro - Columns Are not Hidding / unhiding correctly
Replies
4
Views
433
GarethCW
G
D
  • Question Question
VBA to Remove Text Characters From Strings
Replies
5
Views
689
Fuji
F
D
  • Question Question
Very minor change in code had a dramatic impact on the speed. Why?
Replies
2
Views
806
deZine
D
Guna13
  • Question Question
Help to Optimize Macro Code for Faster Performance
Replies
0
Views
1K
Guna13
Guna13
L
  • Question Question
error 1004 - delete column
Replies
7
Views
1K
LCC
L

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top