Link to home
Create AccountLog in
Avatar of mldaigle1
mldaigle1Flag for Canada

asked on

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
Avatar of [ fanpages ]
[ fanpages ]

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

Avatar of mldaigle1

ASKER

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
@ Rajesh,

I will try you macro upon my return Wednesday afternoon.

:)
ASKER CERTIFIED SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Hi sktneer,

Works like a charm!

Thanks,

:)

/mld
xxx
You're welcome mld! Glad to help. :)