VBA code to delete rows

miva0601

New Member
Joined
Mar 31, 2015
Messages
33
Hello,
I'm trying to add a few steps before the below "Rearrange Order" code is executed, namely, delete entire rows where column AG = 'D' and then delete entire rows where column O starts with a '3'. Can someone help me rewrite the code to delete entire rows given those two criteria and where do I enter it for it to execute on the "variable columns" tab -i.e. before the "Rearrange Order" macro runs and creates "New" tab?


VBA Code:
Sub Rearange_Order()
'
    Sheets("variable colums").Select
    I = Sheets("variable colums").Index
    Sheets.Add
    Sheets(I).Name = "New"
    Last_Col_Fixed = Sheets("fixed columns").Range("IV1").End(xlToLeft).Column
    Last_Col_Variable = Sheets("variable colums").Range("IV1").End(xlToLeft).Column
    I_Col_New = 1
    For I = 1 To Last_Col_Fixed
        Search_Header = Sheets("fixed columns").Cells(1, I)
        Sheets("variable colums").Select
            Set C = Range(Cells(1, 1), Cells(1, Last_Col_Variable)).Find(Search_Header, LookIn:=xlValues)
            If (Not (C) Is Nothing) Then
                Cells(1, C.Column).EntireColumn.Copy Sheets("New").Cells(1, I_Col_New)
                I_Col_New = I_Col_New + 1
            End If
    Next I
End Sub


My attempt at "delete row where column O starts with a '3'. This one doesn't work, my excel just kept spinning

VBA Code:
Sub Delete_Row()
On Error Resume Next
With Range("O1:O" & Range("3" & Rows.Count).End(xlUp).Row)
    .Replace "3*", "#N/A", xlWhole
    .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
End With
End Sub
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
With Range("O1:O" & Range("3" & Rows.Count).End(xlUp).Row)
Seems to me that the syntax in this line isn't correct; this is what doesn't 'fit' me: Range("3" & Rows.Count).End(xlUp).Row
 
Upvote 0
Does this do what you want...

As an aside, you may want to get into the habit of declaring all your variables...

VBA Code:
Sub Rearange_Order()
    Dim Last_Col_Fixed As Long, I_Col_New As Long, i As Long, Last_Col_Variable As Long
    Dim C As Range, Search_Header As String
    Dim x As Long, y As Long
  
    Sheets("variable colums").Select
  
    With Sheets("variable colums")
        For y = .Cells(Rows.Count, "AG").End(xlUp).Row To 1 Step -1
            If Range("AG" & y) = "D" Then Range("AG" & y).EntireRow.Delete
        Next
        For x = .Cells(Rows.Count, "O").End(xlUp).Row To 1 Step -1
            If Range("O" & x) Like "3*" Then Range("O" & x).EntireRow.Delete
        Next
    End With
  
    i = Sheets("variable colums").Index
    Sheets.Add
    Sheets(i).Name = "New"
    Last_Col_Fixed = Sheets("fixed columns").Range("IV1").End(xlToLeft).Column
    Last_Col_Variable = Sheets("variable colums").Range("IV1").End(xlToLeft).Column
    I_Col_New = 1
    For i = 1 To Last_Col_Fixed
        Search_Header = Sheets("fixed columns").Cells(1, i)
        Sheets("variable colums").Select
            Set C = Range(Cells(1, 1), Cells(1, Last_Col_Variable)).Find(Search_Header, LookIn:=xlValues)
            If (Not (C) Is Nothing) Then
                Cells(1, C.Column).EntireColumn.Copy Sheets("New").Cells(1, I_Col_New)
                I_Col_New = I_Col_New + 1
            End If
    Next i
End Sub
 
Upvote 0
Does this do what you want...

As an aside, you may want to get into the habit of declaring all your variables...

VBA Code:
Sub Rearange_Order()
    Dim Last_Col_Fixed As Long, I_Col_New As Long, i As Long, Last_Col_Variable As Long
    Dim C As Range, Search_Header As String
    Dim x As Long, y As Long
 
    Sheets("variable colums").Select
 
    With Sheets("variable colums")
        For y = .Cells(Rows.Count, "AG").End(xlUp).Row To 1 Step -1
            If Range("AG" & y) = "D" Then Range("AG" & y).EntireRow.Delete
        Next
        For x = .Cells(Rows.Count, "O").End(xlUp).Row To 1 Step -1
            If Range("O" & x) Like "3*" Then Range("O" & x).EntireRow.Delete
        Next
    End With
 
    i = Sheets("variable colums").Index
    Sheets.Add
    Sheets(i).Name = "New"
    Last_Col_Fixed = Sheets("fixed columns").Range("IV1").End(xlToLeft).Column
    Last_Col_Variable = Sheets("variable colums").Range("IV1").End(xlToLeft).Column
    I_Col_New = 1
    For i = 1 To Last_Col_Fixed
        Search_Header = Sheets("fixed columns").Cells(1, i)
        Sheets("variable colums").Select
            Set C = Range(Cells(1, 1), Cells(1, Last_Col_Variable)).Find(Search_Header, LookIn:=xlValues)
            If (Not (C) Is Nothing) Then
                Cells(1, C.Column).EntireColumn.Copy Sheets("New").Cells(1, I_Col_New)
                I_Col_New = I_Col_New + 1
            End If
    Next i
End Sub
No, after it deletes rows where column AG is equal to D, it just spins until I 'end task' on excel. The issue isn't with the "Rearrange Order" code from my original post, since if I run this alone, it executes instantly.
 
Upvote 0
That is weird as after it deletes the rows where column AG is equal to D, the code moves on to this loop

VBA Code:
For x = .Cells(Rows.Count, "O").End(xlUp).Row To 1 Step -1
        If Range("O" & x) Like "3*" Then Range("O" & x).EntireRow.Delete
Next

which is a clearly defined loop. Where is the last row of data in Column "O" after all the "D" rows have been deleted...
 
Upvote 0
That is weird as after it deletes the rows where column AG is equal to D, the code moves on to this loop

VBA Code:
For x = .Cells(Rows.Count, "O").End(xlUp).Row To 1 Step -1
        If Range("O" & x) Like "3*" Then Range("O" & x).EntireRow.Delete
Next

which is a clearly defined loop. Where is the last row of data in Column "O" after all the "D" rows have been deleted...
Do you know how to rewrite it so that it doesn't loop?
 
Upvote 0
I have no idea what your data looks like. It should not wind up as an infinite loop. Can you tell me where the last row of data is in Column "O" after all the "D" rows from Column "AG" have been deleted.

Also, if you are saying that the code is just looping over and over, that does not really make sense as my For/Next loop is starting at the last row that contains data in Column "O" and working up the sheet to a definitive end at row 1, so it has to stop there. It can't be just an infinite loop.
 
Last edited:
Upvote 0
I have no idea what your data looks like. It should not wind up as an infinite loop. Can you tell me where the last row of data is in Column "O" after all the "D" rows from Column "AG" have been deleted.

Also, if you are saying that the code is just looping over and over, that does not really make sense as my For/Next loop is starting at the last row that contains data in Column "O" and working up the sheet to a definitive end at row 1, so it has to stop there. It can't be just an infinite loop.
Sorry for the delay! Here's what my data looks like, and I would like VBA code that would delete the entire row where column O starts with a '3'. The number of rows in my data set will change each month.

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAM
1CategoryCityCountry/RegionCustomer NameManufacturerOrder DateOrder IDPostal CodeProduct NameRegionSegmentShip DateShip ModeStateCORP_PRODUCT_CLASSCategory2City3Country/Region4Customer Name5Manufacturer6Order Date7Order ID8Postal Code9Product Name10Region11Segment12Ship Date13Ship Mode14Category15City16Country/Region17Customer Name18Manufacturer19Order Date20Order ID21Postal Code22Product Name23Region24Segment25
2FurnitureHendersonUnited StatesClaire GuteBush11/8/2019CA-2019-15215642420Bush Somerset Collection BookcaseSouthConsumer11/11/2019Second ClassKentucky3AZNFurnitureHendersonUnited StatesClaire GuteBush11/8/2019CA-2019-15215642420Bush Somerset Collection BookcaseSouthConsumer11/11/2019Second ClassFurnitureHendersonUnited StatesClaire GuteBush11/8/2019CA-2019-15215642420Bush Somerset Collection BookcaseSouthConsumer
3FurnitureHendersonUnited StatesClaire GuteHon11/8/2019CA-2019-15215642420Hon Deluxe Fabric Upholstered Stacking Chairs, Rounded BackSouthConsumer11/11/2019Second ClassKentuckyAFFNFurnitureHendersonUnited StatesClaire GuteHon11/8/2019CA-2019-15215642420Hon Deluxe Fabric Upholstered Stacking Chairs, Rounded BackSouthConsumer11/11/2019Second ClassFurnitureHendersonUnited StatesClaire GuteHon11/8/2019CA-2019-15215642420Hon Deluxe Fabric Upholstered Stacking Chairs, Rounded BackSouthConsumer
4Office SuppliesLos AngelesUnited StatesDarrin Van HuffUniversal6/12/2019CA-2019-13868890036Self-Adhesive Address Labels for Typewriters by UniversalWestCorporate6/16/2019Second ClassCaliforniaZZZZOffice SuppliesLos AngelesUnited StatesDarrin Van HuffUniversal6/12/2019CA-2019-13868890036Self-Adhesive Address Labels for Typewriters by UniversalWestCorporate6/16/2019Second ClassOffice SuppliesLos AngelesUnited StatesDarrin Van HuffUniversal6/12/2019CA-2019-13868890036Self-Adhesive Address Labels for Typewriters by UniversalWestCorporate
5FurnitureFort LauderdaleUnited StatesSean O'DonnellBretford10/11/2018US-2018-10896633311Bretford CR4500 Series Slim Rectangular TableSouthConsumer10/18/2018Standard ClassFloridaFurnitureFort LauderdaleUnited StatesSean O'DonnellBretford10/11/2018US-2018-10896633311Bretford CR4500 Series Slim Rectangular TableSouthConsumer10/18/2018Standard ClassFurnitureFort LauderdaleUnited StatesSean O'DonnellBretford10/11/2018US-2018-10896633311Bretford CR4500 Series Slim Rectangular TableSouthConsumer
6Office SuppliesFort LauderdaleUnited StatesSean O'DonnellEldon10/11/2018US-2018-10896633311Eldon Fold 'N Roll Cart SystemSouthConsumer10/18/2018Standard ClassFlorida333HHOffice SuppliesFort LauderdaleUnited StatesSean O'DonnellEldon10/11/2018US-2018-10896633311Eldon Fold 'N Roll Cart SystemSouthConsumer10/18/2018Standard ClassOffice SuppliesFort LauderdaleUnited StatesSean O'DonnellEldon10/11/2018US-2018-10896633311Eldon Fold 'N Roll Cart SystemSouthConsumer
7FurnitureLos AngelesUnited StatesBrosina HoffmanEldon6/9/2017CA-2017-11581290032Eldon Expressions Wood and Plastic Desk Accessories, Cherry WoodWestConsumer6/14/2017Standard ClassCalifornia4GHDKFurnitureLos AngelesUnited StatesBrosina HoffmanEldon6/9/2017CA-2017-11581290032Eldon Expressions Wood and Plastic Desk Accessories, Cherry WoodWestConsumer6/14/2017Standard ClassFurnitureLos AngelesUnited StatesBrosina HoffmanEldon6/9/2017CA-2017-11581290032Eldon Expressions Wood and Plastic Desk Accessories, Cherry WoodWestConsumer
8Office SuppliesLos AngelesUnited StatesBrosina HoffmanNewell6/9/2017CA-2017-11581290032Newell 322WestConsumer6/14/2017Standard ClassCalifornia1111Office SuppliesLos AngelesUnited StatesBrosina HoffmanNewell6/9/2017CA-2017-11581290032Newell 322WestConsumer6/14/2017Standard ClassOffice SuppliesLos AngelesUnited StatesBrosina HoffmanNewell6/9/2017CA-2017-11581290032Newell 322WestConsumer
9TechnologyLos AngelesUnited StatesBrosina HoffmanMitel6/9/2017CA-2017-11581290032Mitel 5320 IP Phone VoIP phoneWestConsumer6/14/2017Standard ClassCaliforniaGHHHTechnologyLos AngelesUnited StatesBrosina HoffmanMitel6/9/2017CA-2017-11581290032Mitel 5320 IP Phone VoIP phoneWestConsumer6/14/2017Standard ClassTechnologyLos AngelesUnited StatesBrosina HoffmanMitel6/9/2017CA-2017-11581290032Mitel 5320 IP Phone VoIP phoneWestConsumer
variable columns
 
Upvote 0
The code I wrote works for me with your provided data. What I did notice is that in your Post #1, you provided code that indicated that the name of the sheet was "variable colums". Please note that you have misspelled "colums". Your code from Post #1 below:

VBA Code:
Sub Rearange_Order()
'
    Sheets("variable colums").Select
    I = Sheets("variable colums").Index
    Sheets.Add

Now that you have posted your data in Post #8, you can see that the true name of the sheet has the word "Columns" spelled correctly, therefore my code should have produced a subscript error. When I download your data and rename the sheet to what I wrote the code for- a sheet named "variable colums", the code works.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

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