Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

I have a column of data in Column A

Status
Not open for further replies.

IcarusHallsorts

Technical User
Nov 8, 2019
9
0
0
GB
I have a column of data in Column A and I want to delete the entire row and move the rows up if the text in the cell contains a certain string.
How do I get VBA to prompt me for the text and then when entered will proceed to delete the relevant rows?
 
Hi,

Are you saying that for the selected cell or for all the rows in the column you want to search for a string and if found delete every row containing that string?

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!
 
Case sensitive:

Code:
Option Explicit

Public Sub DeleteRows()
Dim R As Long
Dim str As String

str = InputBox("What do you want to Delete?")

For R = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
    If InStr(Cells(R, 1).Value, str) > 0 Then
        Rows(R & ":" & R).Delete Shift:=xlUp
    End If
Next R

End Sub

If you want to make it Case insensitive, change line of code to:[tt]
If InStr([blue]UCase([/blue]Cells(R, 1).Value[blue])[/blue], [blue]UCase([/blue]str[blue])[/blue]) > 0 Then[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top