PDA

View Full Version : Deleting Rows w/ specifics datas



pster
05-18-2007, 12:25 PM
Hi, me again! hehe

I have this following code:

[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


but i want anything else instead this msgbox. For examples, I will put in Cells A1:A10 the rows that i want to delete.

tkx!

pster
05-18-2007, 12:30 PM
and also!!!

i want to delete the rows that in column B has a text that is not the one that you put in cell A1

lucas
05-18-2007, 12:41 PM
change this:
Set MyRange = Columns(SearchColumn)
to

Set MyRange = Columns("A")


and change this:
MatchString = InputBox("Enter Search string", "Row Delete Code", ActiveCell.Value)
to(I only set up A1-A4....you can change to suit)
MatchString = Range("A1,A2,A3,A4")

lucas
05-18-2007, 12:42 PM
and also!!!

i want to delete the rows that in column B has a text that is not the one that you put in cell A1
Huh!?

pster
05-18-2007, 12:44 PM
Tkx lucas!

austenr
05-18-2007, 12:44 PM
I think he means that if what is in A1:A10 is also in column B then delete the row. However he does not tell us the range in column B so ???

pster
05-18-2007, 12:45 PM
Huh!?

That was a bit confuse, haha.

What I wanted to say is that,

delete the rows that in column "A" dont hav the text mentioned in cell A1

lucas
05-18-2007, 12:48 PM
So you want this to work the reverse of the way it's set up....delete them if they don't have the text in A1:A10 ??

pster
05-18-2007, 12:54 PM
Yes, thats it!

lucas
05-18-2007, 12:56 PM
per the first question you also have to comment out the input boxes and I just noticed that if you use the matchstring in column A that it deletes those also so I would move those to a different column.

lucas
05-18-2007, 01:00 PM
Why not just filter by multiple criteria?

lucas
05-18-2007, 01:04 PM
I think he means that if what is in A1:A10 is also in column B then delete the row. However he does not tell us the range in column B so ???
Hey austenr,
We'll get to the bottom of it if WE ask enough questions....

lucas
05-18-2007, 01:29 PM
see if this helps...