VBA Code Delete Row

[freaknyea]

Hello. First post at this Forum for me. I found this forum when a co-worker of my told me about it. I have been posting on another forum, but have had no luck.

I am trying to delete a row based on the criteria of the first cell in the row in a workbook.

I have found code to delete a row in a worksheet, but not a workbook.

My workbook includes charts based on other worksheets in the workbook. I don't know if charts makes a difference in running a "search->delete"

Does anyone have a solution?

Thank you.

[Bob Phillips]

A row is part of a worksheet, not a workbook. A worksheet is part of a workbook.

So are you saying that want to search ALL worksheets (not sheets) for a certain value, and if and when found, delete the row that contains that value?

Could it be repetaed? Could it be on more than one worksheet?

[freaknyea]

"So are you saying that want to search ALL worksheets (not sheets) for a certain value, and if and when found, delete the row that contains that value?"


Yes.




"Could it be repetaed? Could it be on more than one worksheet?"


I don't know if I understand this correctly.

[freaknyea]

I used brettdj's Code to delete rows on one worksheet, but I want it to work on all worksheets.

[Aussiebear]

So it needs to loop through, until it no longer finds a certain criteria within Column A of all sheets within the workbook?

What is the criteria?

[freaknyea]

I have messed with this code a few times. Moving things around and it just doesn't seem to work.
In cell A17 I have "Preventative Maintainance". Throughout the row is data. I want to delete every row with Preventative Maintainance in Column A.
But, I don't want to run the macro for just Preventative Maintainance. I would like to run it for whatever cell I select.


Sub KillRows()

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Activate

Dim MyRange As Range, DelRange As Range, C As Range
Dim MatchString As String, SearchColumn As String, ActiveColumn As String
Dim FirstAddress As String, NullCheck As String
Dim AC

'Extract active column as text
AC = Split(ActiveCell.EntireColumn.Address(, False), ":")
ActiveColumn = AC(0)

SearchColumn = InputBox("Enter Search Column - press Cancel to exit sub", "Row Delete Code", ActiveColumn)

On Error Resume Next
Set MyRange = Columns(SearchColumn)
On Error GoTo 0

'If an invalid range is entered then exit
If MyRange Is Nothing Then Exit Sub

MatchString = InputBox("Enter Search string", "Row Delete Code", ActiveCell.Value)
If MatchString = "" Then
NullCheck = InputBox("Do you really want to delete rows with empty cells?" & vbNewLine & vbNewLine & _
"Type Yes to do so, else code will exit", "Caution", "No")
If NullCheck <> "Yes" Then Exit Sub
End If

Application.ScreenUpdating = False

'to match the WHOLE text string
Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, LookAt:=xlWhole)
'to match a PARTIAL text string use this line
'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlpart)
'to match the case and of a WHOLE text string
'Set C = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True)

If Not C Is Nothing Then
Set DelRange = C
FirstAddress = C.Address
Do
Set C = MyRange.FindNext(C)
Set DelRange = Union(DelRange, C)
Loop While FirstAddress <> C.Address
End If

'If there are valid matches then delete the rows
If Not DelRange Is Nothing Then DelRange.EntireRow.Delete

Next

Application.ScreenUpdating = True

End Sub