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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top