Excel Macro - Find & delete row with word "server"

Hello Expert,

I need your help in order to have a macro that will read each row of a table and when the macro find the word "server" somewhere in that row, it will delete that row and continue on next row until it reach the end of the table.

I have 3 tables with more then 10k rows (table1 on sheet1, table2 on sheet2, table3 on sheet3)

Can you help?

:)

/mld
mldaigle1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Hi,

Is this a one-off/single use requirement, or will you need to run the process multiple times on a periodic basis?

Also, please could you describe the nature of the data; the worksheet(s) used, the number of columns per table, including the start column &/or finish column reference, & the starting row number?   Also, does your table have column headings?  Is the search criteria ("server") the only text in the cell(s) on the rows to be interrogated, or is it part of a longer piece of text (i.e. multiple words per cell)?

Alternatively, please provide a small sample of the three tables.

If this is a one-off process, the selection of data to be deleted can be determined using a single in-cell formula (like HLOOKUP) somewhere on the same row as the rest of the data in the table.

The deletion of the matching rows can be handled using an (auto)filter to show just the rows containing "server", & these can be deleted together.

Thank you for your clarification to establish the extent of your requirements.
Rajesh JoshiCommented:
Sub macro1()
 
 For r = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
 For c = 1 To ActiveSheet.UsedRange.Columns.Count
 
 If InStr(LCase(ActiveSheet.Cells(r, c)), LCase("server")) > 0 Then
 
 ActiveSheet.Rows(r).Delete
  
 End If
 
 Next
 Next
 
 
End Sub

Open in new window

mldaigle1Author Commented:
Hello [FanPages],

This job will be run 2 times a month for clean up purposes.
TXT files will be imported into the Excel Workbook
Each table have the same amount of columns (14 columns, A to N) and have a column heading as well, the same one (so data are starting on Row 2).

The word "server" can be found any where on the row and so far, that the only word I need to validate for deletion

As requested, I did attach a sample of each table.

thanks for you help,
table1.txt
table2.txt
table3.txt
10 Holiday Gifts Perfect for Your Favorite Geeks

Still have some holiday shopping to do for the geeks in your life? While toys, clothing, games, and gift cards are still viable options for your friends and family, there’s more reason than ever to consider gadgets and software.

mldaigle1Author Commented:
@ Rajesh,

I will try you macro upon my return Wednesday afternoon.

:)
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this.......
Sub DeleteRows()
Dim ws As Worksheet
Dim lr As Long, i As Long
Application.ScreenUpdating = False
For Each ws In Worksheets
    lr = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For i = lr To 1 Step -1
        If WorksheetFunction.CountIf(ws.Rows(i), "*server*") > 0 Then
            ws.Rows(i).Delete
        End If
    Next i
Next ws
Application.ScreenUpdating = True
End Sub

Open in new window


Before running the code, create a backup of your data or run this macro on a copy of your actual workbook so that you may compare the results after the code execution.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mldaigle1Author Commented:
Hi sktneer,

Works like a charm!

Thanks,

:)

/mld
xxx
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome mld! Glad to help. :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.