![]() |
|
|
|
#1
|
|||
|
|||
|
HI,
Need to automatically delete row that contain certain keywords. Example -: i have data sheet with 5 column.(Name,Last Name,Company Position,Email Address ,Phone Numbers ) If any of keywords are located in any cell (as part of a word, email, address, job title, company, etc,) need to delete the row automatically. (keywords-loan,financial,bank,equity) Thank you. |
|
#2
|
||||
|
||||
|
Let me ask again, is this a one-shot task ?
__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
|
#3
|
|||
|
|||
|
hi,
its not one-shot task. |
|
#4
|
|||
|
|||
|
perhaps something along the lines of this
Code:
Sub RemoveKeyWordRows()
Dim lr As Long, i As Long, cel As Range, temp As String
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "A").End(xlUp).Row
For i = lr To 2 step -1
temp = ""
For Each cel In Range(Cells(i, "A"), Cells(i, "E"))
temp = temp & cel.Value & " "
Next cel
'(keywords-loan,financial,bank,equity)
If InStr(temp, "loan") > 0 Or _
InStr(temp, "financial") > 0 Or _
InStr(temp, "bank") > 0 Or _
InStr(temp, "equity") > 0 _
Then Rows(i).Delete
Next i
Application.ScreenUpdating = True
End Sub
Last edited by NoSparks; 12-06-2017 at 09:55 AM. Reason: step |
|
#5
|
|||
|
|||
|
.
Or maybe : Code:
Option Explicit
Sub sbDelete_Rows_With_Specific_Data()
Dim lRow As Long
Dim iCntr As Long
Dim iCol As Long
iCol = 26 'add cols here
lRow = 20 'add rows here
For iCol = 1 To 26 'match last col number here with total cols above
For iCntr = lRow To 1 Step -1
If Cells(iCntr, iCol) = "loan" Or Cells(iCntr, iCol) = "financial" _
Or Cells(iCntr, iCol) = "bank" Or Cells(iCntr, iCol) = "equity" Then ' You can change this text
Rows(iCntr).Delete
End If
Next
Next
End Sub
|
|
| Tags |
| excel vba, macros |
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
How to delete automatically 500 “Heading 4” and all the contents in this from Navigation?
|
liliaroma | Word VBA | 4 | 11-18-2017 07:53 PM |
| Automatically Delete Slides based on current time | Pacman52 | PowerPoint | 2 | 07-16-2015 05:30 AM |
| How to Automatically Add/Delete Columns Based On Information | kconnolly | Excel | 7 | 04-08-2014 07:23 AM |
| Find and Delete Rows based on a range | damaniam | Excel Programming | 2 | 03-12-2014 06:06 AM |
Find keyword in section & delete section
|
donaldadams1951 | Word VBA | 5 | 12-03-2013 10:08 AM |