How to automatically delete certain rows in Excel?

Creating a Microsoft Excel Macro to Delete Rows That Contain a Certain Word.?

• I am trying to create a macro in ms Excel that will search all rows for the word "First" and remove those rows moving while also bumping all remaining rows up in the spreadsheet. However, I do not want this macro to affect Row # 1 which serves as a column header with things like First Name, Last Name, Email Address etc. I've tried different combinations of macros and protecting/locking row 1, but I can't get this straight. Here is an example of a spreadsheet I need to clean up. In this example I will essentailly be removing every other row, but that will not always be the case: First Name Last Name Email Address (Want to protect this row) First Last Email Marie Nelson mattdkd.p.nelson@helpers.… First Last Email Brentt Nelerson brents.everclson@sartv… First Last Email Stan Lwildlie [email protected] First Last Email Champ Postartal [email protected]… First Last Email Camron Huston [email protected]… First Last Email Wilma Georgtow vsgeosssdfrge@llfsdddu.… First Last Email Stephart Palaton slalsssda1@lsufdddhsc…

• Answer:

  You should do it something like below. The trick is to use a Step when you loop and to set the Step to -1 and work your way from the last row to the first row. The .Row is the first row so I have it loop all of the way through the 2nd row, avoiding your header row whether it be on row 1 or row 17. Sub Macro2() Dim LastRow As Long, LastColumn As Long Dim a As Long, b As Long With ActiveSheet.UsedRange LastRow = .Rows.Count + .Row - 1 LastColumn = .Columns.Count + .Column - 1 For a = LastRow To .Row + 1 Step -1 For b = 1 To LastColumn If Cells(a, b).Value Like "*First*" Then Cells(a, b).EntireRow.Delete Exit For End If Next b Next a End With End Sub