Delete entire row (user defined range and text via inputbox)

[Dustinkli]

Good day,


I've searched high and low to try to find a working VBA for this but I can't seem to find one that meets my needs.


I am trying to write a VBA code or find a VBA code that enables me to select a range of cells and then to specify an input so that it searches that word in the cells and deletes the entire row if it's in the cell. I'd like it to be able to do it based on if the cell contains just that word but also if the cell contains that plus other words. For instance if I put in a "*" before and after the word it'll delete every row if a cell in the specified column has that word anywhere in the cell.


I have this VBA which lets me search "SpecificWord" and deletes all rows that have that in column C, but I'd love to be able to add an input box to it to specify the range and the input value of what to delete. In the below example it searches all cells that have that term anywhere in it. If I remove the "*" at the beginning and end it'll search only if the entire cell is that value.

----------------------------
Sub delete if cell contains()


With ActiveSheet
.AutoFilterMode = False
With Range("C1", Range("C" & Rows.Count).End(xlUp))
.AutoFilter 1, "*SpecificWord*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With


End Sub
----------------------------

 

[Dustinkli]

As a P.S.,

I'm OK with any code that does what I'm trying to do so it doesn't need to really include the code I posted above.

 

[Yongle]

how about ..

Sub delete_if_cell_contains()
    With ActiveSheet
        .AutoFilterMode = False
        With .Range("C1", .Range("C" & Rows.Count).End(xlUp))
            .AutoFilter 1, "*" & InputBox("Search for?") & "*"
            On Error Resume Next
            .Offset(1).SpecialCells(12).EntireRow.delete
        End With
        .AutoFilterMode = False
    End With
End Sub

 

[Dustinkli]

I'll give it a try to see if it fits my needs. Thanks. Will let ya know.

 

[Dustinkli]

Is there a way to have the 1st inputbox ask for the range and the 2nd inputbox to ask for the word?

 

[Dustinkli]

This code works but it doesn't allow for wildcard * in the input box:

--------
Sub DeleteRows()
Dim rng As Range
Dim InputRng As Range
Dim DeleteRng As Range
Dim DeleteStr As String
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
DeleteStr = Application.InputBox("Delete Text", xTitleId, Type:=2)
For Each rng In InputRng
If rng.Value = DeleteStr Then
If DeleteRng Is Nothing Then
Set DeleteRng = rng
Else
Set DeleteRng = Application.Union(DeleteRng, rng)
End If
End If
Next
DeleteRng.EntireRow.Delete
End Sub
----------


Not sure why.

 

[Yongle]

Is there a way to have 'the 1st inputbox ask for the range and the 2nd inputbox to ask for the word?

Try selecting the range BEFORE running this

Sub delete_if_cell_contains()
    With ActiveSheet
        .AutoFilterMode = False
        With Selection
            .AutoFilter 1, "*" & InputBox("Search for?") & "*"
            On Error Resume Next
            .Offset(1).SpecialCells(12).EntireRow.Delete
        End With
        .AutoFilterMode = False
    End With
End Sub

 

[Dustinkli]

Hi Yongle,

It doesn't seem to work. When I select it before running it nothing happens. If I select nothing and I put a word in it only takes 1 row with that word out and doesn't loop from the bottom up.

My goal is for it to take out every row with that particular word if I specify an exact cell content.

I'd also like it to accept wildcard * so that if I put * before or after the word in the input box it'll search that way as well but only if I put *.

 

[Yongle]

I clearly do not understand what you are doing because the macro does exactly what it should for me as the images below demonstrate

Replicate EXACTLY what I have done so that you can see it working, and then tell me what differs in your requirements

1. BEFORE

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​	M​	N​
1​	hdre1	hdre2	hdre3	hdre4	hdre5	hdre6	hdre7	hdre8	hdre9	hdre10	hdre11	hdre12	hdre13	hdre14
2​	5002​	7910​	9628​	5046​	8637​	1718​	2174​	1202​	8049​	3216​	4755​	7626​	7592​	Apple
3​	6965​	8357​	7205​	7597​	1005​	4803​	8928​	2675​	3924​	7750​	7681​	7165​	9425​	Orange
4​	4720​	9415​	5884​	6365​	3010​	4424​	6904​	9696​	9688​	6970​	9510​	8563​	9508​	Apple
5​	4033​	4915​	1184​	9521​	6689​	1894​	1203​	1991​	6468​	7294​	1167​	9109​	8139​	Orange
6​	6435​	1192​	7117​	8211​	8455​	6858​	6288​	9631​	7549​	1771​	2425​	8128​	3843​	Apple
7​	6148​	8037​	6493​	8314​	8563​	9736​	3339​	6624​	6718​	8518​	2782​	4273​	2137​	Orange
8​	6568​	5891​	6826​	5639​	7462​	2826​	7158​	6291​	3168​	3935​	5920​	3205​	9288​	Apple
9​	1940​	4793​	3722​	8357​	1093​	5908​	8930​	3439​	4628​	5075​	6609​	1796​	5029​	Orange
10​	2072​	7230​	4990​	7065​	2945​	1178​	9514​	6654​	9677​	9125​	9521​	4096​	6812​	Banana
11​	2269​	4187​	7398​	1057​	9726​	4374​	5944​	3724​	9534​	8545​	4351​	3158​	3033​	Banana
12​	9087​	6245​	7951​	7622​	8180​	5618​	3014​	6650​	5192​	5554​	4609​	2760​	7213​	Banana
13​	9957​	4186​	4125​	1941​	3210​	7482​	5885​	2339​	8292​	6940​	3004​	6271​	7715​	Banana
14​	3003​	4524​	2010​	2658​	2789​	9391​	5294​	2675​	9550​	8305​	6349​	7103​	1912​	Banana
15​	8885​	1840​	6353​	8656​	1385​	6001​	6173​	1173​	5085​	4163​	5915​	5208​	6025​	Banana

Sheet: Sheet20

2. SELECT N1:N15 and type apple in the box and click OK

3. AFTER

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​	M​	N​
1​	hdre1	hdre2	hdre3	hdre4	hdre5	hdre6	hdre7	hdre8	hdre9	hdre10	hdre11	hdre12	hdre13	hdre14
2​	6965​	8357​	7205​	7597​	1005​	4803​	8928​	2675​	3924​	7750​	7681​	7165​	9425​	Orange
3​	4033​	4915​	1184​	9521​	6689​	1894​	1203​	1991​	6468​	7294​	1167​	9109​	8139​	Orange
4​	6148​	8037​	6493​	8314​	8563​	9736​	3339​	6624​	6718​	8518​	2782​	4273​	2137​	Orange
5​	1940​	4793​	3722​	8357​	1093​	5908​	8930​	3439​	4628​	5075​	6609​	1796​	5029​	Orange
6​	2072​	7230​	4990​	7065​	2945​	1178​	9514​	6654​	9677​	9125​	9521​	4096​	6812​	Banana
7​	2269​	4187​	7398​	1057​	9726​	4374​	5944​	3724​	9534​	8545​	4351​	3158​	3033​	Banana
8​	9087​	6245​	7951​	7622​	8180​	5618​	3014​	6650​	5192​	5554​	4609​	2760​	7213​	Banana
9​	9957​	4186​	4125​	1941​	3210​	7482​	5885​	2339​	8292​	6940​	3004​	6271​	7715​	Banana
10​	3003​	4524​	2010​	2658​	2789​	9391​	5294​	2675​	9550​	8305​	6349​	7103​	1912​	Banana
11​	8885​	1840​	6353​	8656​	1385​	6001​	6173​	1173​	5085​	4163​	5915​	5208​	6025​	Banana
12​
13​
14​
15​

Sheet: Sheet20

 

[Dustinkli]

It's working now as I was previously selecting the entire column rather than all of the cells in that column.

However, what I'm shooting for is to have it give me the option to use the * wildcard rather than built into the code. I don't always require it. Is that possible?

Thank you.
​