VBA code to delete rows

[miva0601]

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?

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

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

 

[rollis13]

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

 

[igold]

Does this do what you want...

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

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

 

[miva0601]

Does this do what you want...

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

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.

 

[igold]

That is weird as after it deletes the rows where column AG is equal to D, the code moves on to this loop

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...

 

[miva0601]

That is weird as after it deletes the rows where column AG is equal to D, the code moves on to this loop

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?

 

[igold]

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.

 

[miva0601]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
1	Category	City	Country/Region	Customer Name	Manufacturer	Order Date	Order ID	Postal Code	Product Name	Region	Segment	Ship Date	Ship Mode	State	CORP_PRODUCT_CLASS	Category2	City3	Country/Region4	Customer Name5	Manufacturer6	Order Date7	Order ID8	Postal Code9	Product Name10	Region11	Segment12	Ship Date13	Ship Mode14	Category15	City16	Country/Region17	Customer Name18	Manufacturer19	Order Date20	Order ID21	Postal Code22	Product Name23	Region24	Segment25
2	Furniture	Henderson	United States	Claire Gute	Bush	11/8/2019	CA-2019-152156	42420	Bush Somerset Collection Bookcase	South	Consumer	11/11/2019	Second Class	Kentucky	3AZN	Furniture	Henderson	United States	Claire Gute	Bush	11/8/2019	CA-2019-152156	42420	Bush Somerset Collection Bookcase	South	Consumer	11/11/2019	Second Class	Furniture	Henderson	United States	Claire Gute	Bush	11/8/2019	CA-2019-152156	42420	Bush Somerset Collection Bookcase	South	Consumer
3	Furniture	Henderson	United States	Claire Gute	Hon	11/8/2019	CA-2019-152156	42420	Hon Deluxe Fabric Upholstered Stacking Chairs, Rounded Back	South	Consumer	11/11/2019	Second Class	Kentucky	AFFN	Furniture	Henderson	United States	Claire Gute	Hon	11/8/2019	CA-2019-152156	42420	Hon Deluxe Fabric Upholstered Stacking Chairs, Rounded Back	South	Consumer	11/11/2019	Second Class	Furniture	Henderson	United States	Claire Gute	Hon	11/8/2019	CA-2019-152156	42420	Hon Deluxe Fabric Upholstered Stacking Chairs, Rounded Back	South	Consumer
4	Office Supplies	Los Angeles	United States	Darrin Van Huff	Universal	6/12/2019	CA-2019-138688	90036	Self-Adhesive Address Labels for Typewriters by Universal	West	Corporate	6/16/2019	Second Class	California	ZZZZ	Office Supplies	Los Angeles	United States	Darrin Van Huff	Universal	6/12/2019	CA-2019-138688	90036	Self-Adhesive Address Labels for Typewriters by Universal	West	Corporate	6/16/2019	Second Class	Office Supplies	Los Angeles	United States	Darrin Van Huff	Universal	6/12/2019	CA-2019-138688	90036	Self-Adhesive Address Labels for Typewriters by Universal	West	Corporate
5	Furniture	Fort Lauderdale	United States	Sean O'Donnell	Bretford	10/11/2018	US-2018-108966	33311	Bretford CR4500 Series Slim Rectangular Table	South	Consumer	10/18/2018	Standard Class	Florida		Furniture	Fort Lauderdale	United States	Sean O'Donnell	Bretford	10/11/2018	US-2018-108966	33311	Bretford CR4500 Series Slim Rectangular Table	South	Consumer	10/18/2018	Standard Class	Furniture	Fort Lauderdale	United States	Sean O'Donnell	Bretford	10/11/2018	US-2018-108966	33311	Bretford CR4500 Series Slim Rectangular Table	South	Consumer
6	Office Supplies	Fort Lauderdale	United States	Sean O'Donnell	Eldon	10/11/2018	US-2018-108966	33311	Eldon Fold 'N Roll Cart System	South	Consumer	10/18/2018	Standard Class	Florida	333HH	Office Supplies	Fort Lauderdale	United States	Sean O'Donnell	Eldon	10/11/2018	US-2018-108966	33311	Eldon Fold 'N Roll Cart System	South	Consumer	10/18/2018	Standard Class	Office Supplies	Fort Lauderdale	United States	Sean O'Donnell	Eldon	10/11/2018	US-2018-108966	33311	Eldon Fold 'N Roll Cart System	South	Consumer
7	Furniture	Los Angeles	United States	Brosina Hoffman	Eldon	6/9/2017	CA-2017-115812	90032	Eldon Expressions Wood and Plastic Desk Accessories, Cherry Wood	West	Consumer	6/14/2017	Standard Class	California	4GHDK	Furniture	Los Angeles	United States	Brosina Hoffman	Eldon	6/9/2017	CA-2017-115812	90032	Eldon Expressions Wood and Plastic Desk Accessories, Cherry Wood	West	Consumer	6/14/2017	Standard Class	Furniture	Los Angeles	United States	Brosina Hoffman	Eldon	6/9/2017	CA-2017-115812	90032	Eldon Expressions Wood and Plastic Desk Accessories, Cherry Wood	West	Consumer
8	Office Supplies	Los Angeles	United States	Brosina Hoffman	Newell	6/9/2017	CA-2017-115812	90032	Newell 322	West	Consumer	6/14/2017	Standard Class	California	1111	Office Supplies	Los Angeles	United States	Brosina Hoffman	Newell	6/9/2017	CA-2017-115812	90032	Newell 322	West	Consumer	6/14/2017	Standard Class	Office Supplies	Los Angeles	United States	Brosina Hoffman	Newell	6/9/2017	CA-2017-115812	90032	Newell 322	West	Consumer
9	Technology	Los Angeles	United States	Brosina Hoffman	Mitel	6/9/2017	CA-2017-115812	90032	Mitel 5320 IP Phone VoIP phone	West	Consumer	6/14/2017	Standard Class	California	GHHH	Technology	Los Angeles	United States	Brosina Hoffman	Mitel	6/9/2017	CA-2017-115812	90032	Mitel 5320 IP Phone VoIP phone	West	Consumer	6/14/2017	Standard Class	Technology	Los Angeles	United States	Brosina Hoffman	Mitel	6/9/2017	CA-2017-115812	90032	Mitel 5320 IP Phone VoIP phone	West	Consumer
variable columns

 

[igold]

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:

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.