Search for multiple text in column starting at C3, delete rows if found

288enzo

Active Member
Joined
Feb 8, 2009
Messages
418
Office Version
  1. 2016
Platform
  1. Windows
Hi all -

I've found plenty of examples on how to search for text in a column and delete said column if text is found. My case is slightly different, I need the starting position of the search to begin starting at C3, and searching for two words.

I found this example, of how to search the entire column for one word.

VBA Code:
    Dim lr As Long
    Dim Found As Range
    lr = Range("C" & Rows.Count).End(xlUp).Row
    Set Found = Columns("C").Find(what:="Parts", LookIn:=xlValues, lookat:=xlWhole) ' also need to search for "Certified"
    If Not Found Is Nothing Then Rows(Found.Row & ":" & lr).Delete
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,139
Office Version
  1. 365
Platform
  1. Windows
Can you post a sample of your data, and your expected output, so we can clearly see what the data you are working with looks like, and what you want it to do?
 

288enzo

Active Member
Joined
Feb 8, 2009
Messages
418
Office Version
  1. 2016
Platform
  1. Windows
Can you post a sample of your data, and your expected output, so we can clearly see what the data you are working with looks like, and what you want it to do?
This is the before data
Book5
BC
1 - Parts
2Code - NameCertified
3TEXT21
4TEXT1
5TEXT1
6TEXT3
7TEXT5
8TEXT0
9TEXT0
10TEXT2
11TEXT3
12TEXT0
13TEXT2
14TEXT3
15TEXT1
16 - Parts
17Code - NameCertified
18TEXT55
19TEXT21
20TEXT4
21TEXT3
22TEXT3
23TEXT3
24TEXT3
25TEXT1
26TEXT3
27TEXT1
28TEXT34
29TEXT0
30TEXT1
31TEXT4
32TEXT3
33TEXT2
34TEXT2
35TEXT3
36TEXT4
37TEXT2
38TEXT3
39TEXT3
40TEXT3
41TEXT4
42TEXT0
43 - Parts
44Code - NameCertified
45TEXT42
46TEXT17
47TEXT1
48TEXT0
49TEXT3
50TEXT1
51TEXT0
52TEXT1
53TEXT3
54TEXT4
55TEXT0
56TEXT1
57TEXT1
58TEXT2
59TEXT25
60TEXT0
61TEXT4
62TEXT3
63TEXT2
64TEXT2
65TEXT0
66TEXT2
67TEXT4
68TEXT1
69TEXT4
70TEXT3
71TEXT0
72 - Parts
73Code - NameCertified
74TEXT45
75TEXT28
76TEXT6
77TEXT1
78TEXT8
79TEXT1
80TEXT1
81TEXT0
82TEXT3
83TEXT1
84TEXT5
85TEXT2
86TEXT0
87TEXT0
88TEXT17
89TEXT3
90TEXT0
91TEXT3
92TEXT3
93TEXT5
94TEXT1
95TEXT2
96TEXT0
97TEXT0
98 - Parts
99Code - NameCertified
100TEXT27
101TEXT0
102TEXT1
103TEXT3
104TEXT8
105TEXT6
106TEXT2
107TEXT2
108TEXT0
109TEXT2
110TEXT1
111TEXT1
112TEXT1
113 - Parts
114Code - NameCertified
Sheet1


This is what I would like it to look after
Book5
BC
1 - Parts
2Code - NameCertified
3TEXT21
4TEXT1
5TEXT1
6TEXT3
7TEXT5
8TEXT0
9TEXT0
10TEXT2
11TEXT3
12TEXT0
13TEXT2
14TEXT3
15TEXT1
16TEXT55
17TEXT21
18TEXT4
19TEXT3
20TEXT3
21TEXT3
22TEXT3
23TEXT1
24TEXT3
25TEXT1
26TEXT34
27TEXT0
28TEXT1
29TEXT4
30TEXT3
31TEXT2
32TEXT2
33TEXT3
34TEXT4
35TEXT2
36TEXT3
37TEXT3
38TEXT3
39TEXT4
40TEXT0
41TEXT42
42TEXT17
43TEXT1
44TEXT0
45TEXT3
46TEXT1
47TEXT0
48TEXT1
49TEXT3
50TEXT4
51TEXT0
52TEXT1
53TEXT1
54TEXT2
55TEXT25
56TEXT0
57TEXT4
58TEXT3
59TEXT2
60TEXT2
61TEXT0
62TEXT2
63TEXT4
64TEXT1
65TEXT4
66TEXT3
67TEXT0
68TEXT45
69TEXT28
70TEXT6
71TEXT1
72TEXT8
73TEXT1
74TEXT1
75TEXT0
76TEXT3
77TEXT1
78TEXT5
79TEXT2
80TEXT0
81TEXT0
82TEXT17
83TEXT3
84TEXT0
85TEXT3
86TEXT3
87TEXT5
88TEXT1
89TEXT2
90TEXT0
91TEXT0
92TEXT27
93TEXT0
94TEXT1
95TEXT3
96TEXT8
97TEXT6
98TEXT2
99TEXT2
100TEXT0
101TEXT2
102TEXT1
103TEXT1
104TEXT1
Sheet1
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,139
Office Version
  1. 365
Platform
  1. Windows
Looks like we can just delete all rows that do not have numeric values in column C.
This code will do that:
VBA Code:
Sub MyDeleteRows()

    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column C with data
    lr = Cells(Rows.Count, "C").End(xlUp).Row
    
'   Loop through all rows and delete rows that don't have numbers in column C
    For r = lr To 3 Step -1
        If Not IsNumeric(Cells(r, "C")) Then Rows(r).Delete
    Next r
    
    Application.ScreenUpdating = True
    
End Sub
 
Solution

288enzo

Active Member
Joined
Feb 8, 2009
Messages
418
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Looks like we can just delete all rows that do not have numeric values in column C.
This code will do that:
VBA Code:
Sub MyDeleteRows()

    Dim lr As Long
    Dim r As Long
   
    Application.ScreenUpdating = False
   
'   Find last row in column C with data
    lr = Cells(Rows.Count, "C").End(xlUp).Row
   
'   Loop through all rows and delete rows that don't have numbers in column C
    For r = lr To 3 Step -1
        If Not IsNumeric(Cells(r, "C")) Then Rows(r).Delete
    Next r
   
    Application.ScreenUpdating = True
   
End Sub
Is For r = lr To 3 Step -1 telling the search to start on row 3? If so, why Step -1?

Thank you
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,139
Office Version
  1. 365
Platform
  1. Windows
Is For r = lr To 3 Step -1 telling the search to start on row 3? If so, why Step -1?

Thank you
Not quite, it is telling you to END at row three.
Note that we are looping through the rows backwards, from the last row up to row 3.

When you do a For loop, the default is "Step 1", so it goes up in increasing order one at a time.
Since this is the default, you can leave "Step 1" off, and it will know what to do.
But if you wanted to go in any other order, like skipping and doing every other row ("Step 2") or going in reverse order ("Step -1"), you need to specify it.

When deleting or inserting rows, you should alway loop through your rows in reverse order.
Otherwise, you are changing the size of the range you haven't touched yet, which can lead to some rows getting skipped (i.e. if you have two or more consecutive deletes) or done multiple times (for inserts).
 

288enzo

Active Member
Joined
Feb 8, 2009
Messages
418
Office Version
  1. 2016
Platform
  1. Windows
Not quite, it is telling you to END at row three.
Note that we are looping through the rows backwards, from the last row up to row 3.

When you do a For loop, the default is "Step 1", so it goes up in increasing order one at a time.
Since this is the default, you can leave "Step 1" off, and it will know what to do.
But if you wanted to go in any other order, like skipping and doing every other row ("Step 2") or going in reverse order ("Step -1"), you need to specify it.

When deleting or inserting rows, you should alway loop through your rows in reverse order.
Otherwise, you are changing the size of the range you haven't touched yet, which can lead to some rows getting skipped (i.e. if you have two or more consecutive deletes) or done multiple times (for inserts).
That makes total sense, you want to delete while moving up. Thank you

If I wanted the same to look for text, would I do something like this?
If "TRUE"(Cells(r, "C")) Then Rows(r).Delete
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,139
Office Version
  1. 365
Platform
  1. Windows
If I wanted the same to look for text, would I do something like this?
If "TRUE"(Cells(r, "C")) Then Rows(r).Delete
I think maybe this is what you mean ("TRUE" is not a valid function in Excel, it is a value):
Excel Formula:
If (Cells(r, "C")) = "TRUE" Then Rows(r).Delete
 

Forum statistics

Threads
1,148,280
Messages
5,745,826
Members
423,981
Latest member
ph1l

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
Top