View Full Version : VBA Code Delete Row
freaknyea
01-30-2008, 07:40 AM
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
01-30-2008, 08:00 AM
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
01-30-2008, 08:08 AM
"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
01-30-2008, 08:46 AM
I used brettdj's Code to delete rows on one worksheet, but I want it to work on all worksheets.
Aussiebear
01-30-2008, 12:03 PM
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
01-30-2008, 12:32 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.