PDA

View Full Version : Delete row if cell does not contain a particular value



gxr
05-10-2008, 08:48 PM
Hello all,

I am a new member. I found the following code to delete a row based on a search string. However, I need the code to delete a row if a column does not contain a particular value. Any pointers ?

-gxr

Option Explicit

Sub KillRows()

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

Application.ScreenUpdating = True

End Sub

gxr
05-10-2008, 08:54 PM
I would like to add that I found the above highly useful code at:
<<< Ok, the board doesnt allow me to post links..since I dont have 5 posts yet. So, Cant post the exact link>>>

I need some one to tweak this for me. I am not a vba guy and learnt my hello world program yesterday. I need the above funcationality for a spreadsheet that I am currently working with.

Bob Phillips
05-11-2008, 02:01 AM
Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim Cols As Variant
Dim Col As String
Dim LookFor As String
Dim rng As Range

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

Cols = Split(ActiveCell.EntireColumn.Address(, False), ":")

Col = InputBox("Enter Search Column - Cancel to exit sub", _
"Row Delete Code", Cols(0))

On Error Resume Next
Set rng = Columns(Col)
On Error GoTo 0

If rng Is Nothing Then Exit Sub

LookFor = InputBox("Enter Search string", "Row Delete Code", ActiveCell.Value)

Set rng = Nothing
LastRow = .Cells(.Rows.Count, Col).End(xlUp).Row
For i = LastRow To 1 Step -1

If .Cells(i, Col).Value <> LookFor Then

If rng Is Nothing Then

Set rng = .Rows(i)
Else

Set rng = Union(rng, .Rows(i))
End If
End If
Next i

If Not rng Is Nothing Then rng.Delete
End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub