Delete Rows containing pop-up entry

[Guest]

Hi All......

I got this from a search of this group, and it works fine.

Hi Lawlerd, this code was posted by Frank Kabel, it should do what you
you need, it will delete every row with the word 'total' in column A...

Sub delete_rows()
Dim RowNdx As Long
Dim LastRow As Long
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "A").Value = "total" Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub

My question is, can it be modified to give me a pop-up requesting a value to
search for and delete the rows containing it, instead of a hard-coded "total"?

Many TIA's
Vaya con Dios,
Chuck, CABGx3

 

[Paul B]

Here is one way

Sub delete_rows()
Dim RowNdx As Long
Dim LastRow As Long
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
myvalve = InputBox("What do you want to search for?")
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "A").Value = myvalve Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Jason Morin]

Sub delete_rows()
Dim SearchVal As String
Dim RowNdx As Long
Dim LastRow As Long
SearchVal = InputBox("Enter word:")
If SearchVal = "" Then Exit Sub
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "A").Value = SearchVal Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub

 

[JE McGimpsey]

one way:

Public Sub delete_rows()
Dim vResult As Variant
Dim RowNdx As Long
Dim LastRow As Long
Do
vResult = Application.InputBox( _
Prompt:="Enter search term: ", _
Default:="", _
Title:="Delete Rows", _
Type:=2)
If vResult = False Then Exit Sub 'User cancelled
Loop Until Len(vResult) > 0
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "A").Value = vResult Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub


Note that this returns vResult as a string, so it won't find a numeric
entry (change the Type in the InputBox accordingly, or check the string
representation of the cell instead, e.g.,

If CStr(Cells(RowNdx, "A").Value) = vResult Then

 

[Tom Ogilvy]

You received many good answers, but another approach might be to delete any
entries in the column of the activecell that match the active cell. So you
indicate your value preference and column to search by selecting a single
cell and executing the macro.

Sub delete_rows()
Dim RowNdx As Long
Dim LastRow As Long
Dim vVal as Variant
Dim iCol as long
vVal = ActiveCell
iCol = ActiveCell.Column
if Selection.Columns.Count > 1 then exit sub
LastRow = ActiveSheet.Cells(Rows.Count, icol).End(xlUp).row
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, icol).Value = vVal Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub

 

[Sharad Naik]

If you want to match whole word, entered then:

Dim myRange As Range, strFind As String, c
strFind = InputBox("Enter the text to find", "RowKiller")
If Len(strFind) = 0 Then Exit Sub 'user canceld or entered nothing
With ActiveSheet.UsedRange
Set myRange = .Cells(.Rows.Count + 1, 1)
For Each c In .Cells
If Trim(c.Value) = Trim(strFind) Then
Set myRange = Union(myRange, c)
End If
Next c
End With
If myRange.Cells.Count > 1 Then
myRange.EntireRow.Delete
Else
MsgBox "The entered text was not found in any of the cells.",
vbCritical, "RowKiller"
End If
End Sub

OR if you want to march even part of the cell contents to the entered text
then

Sub RowKiller2()
Dim myRange As Range, strFind As String, c
strFind = InputBox("Enter the text to find", "RowKiller")
If Len(strFind) = 0 Then Exit Sub 'user canceled or entered nothing
With ActiveSheet.UsedRange
Set myRange = .Cells(.Rows.Count + 1, 1)
For Each c In .Cells
If InStr(1, Trim(c.Value), Trim(strFind)) > 0 Then
Set myRange = Union(myRange, c)
End If
Next c
End With
If myRange.Cells.Count > 1 Then
myRange.EntireRow.Delete
Else
MsgBox "The entered text was not found in any of the cells.",
vbCritical, "RowKiller"
End If
End Sub

 

[Guest]

Thanks ever so much Guys....Paul B, Jason, and JE.......all three worked
fine-fine-superfine. I think I will go with Jason's tho, because it seems to
work with either numbers or case-senitive text.

Thanks again for the rapid and working responses......any one of which would
have served me, but it's always nice having a choice.

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

Thank you Tom.....that was beautiful, just beautiful........you never cease
to amaze me. You seem to know what I want even better than I do.

Thanks again,
Vaya con Dios,
Chuck, CABGx3

 

[Guest]

Thank you for your solution Sharad.........I especially like your error
message in case the search is not successful.

Thaks again,
Vaya con Dios,
Chuck, CABGx3

 

[Ron de Bruin]

For a Add-in solution see
http://www.rondebruin.nl/easyfilter.htm

 

[Guest]

Thanks much Ron..........unfortunately I have to work with Excel97 here.
But, I've downloaded your Add-in and will put it to good use at home.

Thanks again,
Vaya con Dios,
Chuck, CABGx3

 

[Ron de Bruin]

Hi

unfortunately I have to work with Excel97

After I have finish the new version with a Date tab for filtering on
months ,Year, week numbers, weekdays, ................
I will see if I can fix the problems in Excel 97.

 

[Guest]

cool........thanks

Vaya con Dios,
Chuck, CABGx3

 

[Tom Ogilvy]

I will see if I can fix the problems in Excel 97.

That would be great Ron, but most users would probably be happy if you could
just get your Addin to work with Excel 97 with its current problems. <G>

--
Regards,
Tom Ogilvy

 

[Ron de Bruin]

Hi Tom

I hope to have the Beta 2.0 version ready this week with the Date tab.
After that I will try to fix the 97 problem.
Maybe i need some help from a Excel 97 expert named Tom<vbg>

 

[Tom Ogilvy]

I might be able to lend a hand.

 

[Ron de Bruin]

Thanks Tom