Macro for deleting rows based on text in a column

kelly247

New Member
Joined
Dec 2, 2005
Messages
10
I'm a fairly advanced excel user but I have no VBA background and have only created macros using the macro recorder.

I would like to create a macro that searches column J for any cells that contain the text "TRAVEL" and then deletes the entire row(s) where "TRAVEL" was found to be in column J.

I am thinking that this is probably quite simple but I haven't been able to figure it out on my own.

Thanks,
Kelly
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,057
Office Version
  1. 365
Platform
  1. Windows
Maybe something like this.
Code:
Dim c As Range
Dim LastRow As Long
Dim I As long
LastRow = Range("J" & Rows.Count).End(xlUp).Row

For I = Lastrow To 1 Step -1

   Set c = Range("J" & I)
   If c.Value = "TRAVEL" Then
      c.EntireRow.Delete
   End If
Next I
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
Hello Kelly, welcome to the board.
This will go through column J and delete the row(s) that contain the word "travel" anywhere in the cell.
Code:
Sub DeleteRowsDemo()
Dim y As Range, x As Range
Dim Rng As Range
Set Rng = Range("J1", Cells(Rows.Count, 10).End(xlUp))
Application.ScreenUpdating = False
For Each y In Rng
  If InStr(y, "TRAVEL") Then
    If x Is Nothing Then
      Set x = y
    Else
      Set x = Application.Union(x, y)
    End If
  End If
Next
x.EntireRow.Delete
Application.ScreenUpdating = True
End Sub
If you do not want it to be case sensitive, simply put this line at the top of the module:
Option Compare text

Hope it helps.
 

kelly247

New Member
Joined
Dec 2, 2005
Messages
10

ADVERTISEMENT

HalfAce,

Your code worded as well. I am trying to learn from both the replys posted, so that I can see how they work and how they differ...

Many thanks,

Kelly
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
Well, the main difference is that Norie's code loops up column J from the bottom to top. This is very important when deleting rows like that (one at a time as they are found) in order to catch all consecutive rows that meet the delete criteria. (Notice the reference to For I = Lastrow To 1 Step -1.

What I did was loop down the column from top to bottom, and tell it to just "remember" each row that met the criteria and then delete them all at once after it's done looping.

Two different perfectly good methods to get the same result.

That help any?
 

kelly247

New Member
Joined
Dec 2, 2005
Messages
10
Yes, it helps, but I have a lot to learn. I am going to buy a book or two on VBA so I can get started with the basics. If anyone has any suggestion for a good resource for beginners, my ears are wide open.

BTW, this is my first time on this Board and everyone is such a great help. I appreciate the time taken to answer my simple question.

Thanks,

Kelly
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
I guess (since you're trying to learn) there are a couple more differences that maybe should be pointed out.
In Norie's code the row will be deleted if the entire cell value is (exactly) TRAVEL. Anything else or a different text case will be skipped.

In the code I posted the row will be deleted if the cell contains the word travel, whether it's the only word in the cell or not. For instance if it finds "travel" or "traveling" or "air travel" these will all be deleted. (Note the reference to: If InStr which translates to "If In String...") which may or may not be desirable depending on your situation.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,451
Messages
5,572,193
Members
412,447
Latest member
immy
Top