Need macro to find text then delete the row it's in

Topic

New Reply

I have an upcoming project to develop a database of acronyms and their definitions. The potential input data will be in a three column table. I need to delete every row that has an X in the third column. I may also need to delete, with a different macro, any row for which column two is empty. If someone can please help me with one, maybe I can figure out the other—as part of my very slow vba learning process.

(Oh, how I still wish for the WordPerfect macro language. I could have figured this out ages ago. )

Pam

Reply | Quote

Replies

Here you go:

Code:

Sub DelX()
Dim lRow As Long, i As Long
lRow = Cells(Rows.Count, 3).End(xlUp).Row
For i = lRow To 2 Step -1
	If Cells(i, 3) = "X" Then
	Cells(i, 3).EntireRow.Delete
	End If
Next

End Sub

Reply | Quote

Here you go:

Code:

Sub DelX()
Dim lRow As Long, i As Long
lRow = Cells(Rows.Count, 3).End(xlUp).Row
For i = lRow To 2 Step -1
	If Cells(i, 3) = "X" Then
	Cells(i, 3).EntireRow.Delete
	End If
Next

End Sub

Hi mbarron,

Your code might work for Excel, but not for Word …

Pam:
Try something along the lines of:

Code:

Sub DelXRows()
Dim oTbl As Table, oCel As Cell, oRng As Range, StrTxt As String
StrTxt = "X"
For Each oTbl In ActiveDocument.Tables
  For Each oCel In oTbl.Columns(3).Cells
	Set oRng = oCel.Range
	oRng.MoveEnd Unit:=wdCharacter, Count:=-1
	If oRng.Text = StrTxt Then oRng.Rows(1).Delete
  Next
Next
End Sub

For the later process, change “X” to “” and Columns(3) to Columns(2).

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Paul and mbarron,

Thank you so much. I knew you guys at Woody’s would come through for me. I’ll try it out as soon as I get to work this morning. I may have a use for one for Excel too.

Many kind regards,
Pam

Reply | Quote

Oops, sorry about that. I didn’t check the forum the post was in – I only read the subject.

Reply | Quote