Quickly delete rows in a range containing certain value

[bran]

Hi, this is my first post so please let me know any mistakes.

I have a worksheet of 10,000 rows containing data seperated into multiple columns. I want to select all the cells in one column that don't contain a user input value and then delete all the rows containing those cells.

I can do this with a loop but it takes far too long. I'm really looking for any method that can quickly get rid of the unwanted rows that doesn't use loops.

 

[VoG]

Try like this

Sub Del_x()
Dim x
x = InputBox("Enter criterion")
With Columns("W")
    .AutoFilter field:=1, Criteria1:=x
    .Resize(Rows.Count - 1).Offset(1).EntireRow.Delete
End With
ActiveSheet.AutoFilterMode = False
End Sub

 

[JackDanIce]

Welcome to the board.

You don't say what the value criteria is that you want to find to delete (blank cell if non user input?). A fast way to delete the rows is to filter and then delete the visible rows. Have a read of:

http://www.ozgrid.com/VBA/row-delete-criteria.htm

 

[bran]

Thanks, I have the right idea now.

However, I wanted to delete every row without the input value. So if I had entered "prog" as the value, everything row not containing "prog" in a the column would be deleted.

 

[JackDanIce]

So change Vog's code to:

Sub Del_x()
Dim x
x = InputBox("Enter criterion")
With Columns("W")
    .AutoFilter field:=1, Criteria1:[COLOR="Red"][B]<>[/B][/COLOR]x
    .Resize(Rows.Count - 1).Offset(1).EntireRow.Delete
End With
ActiveSheet.AutoFilterMode = False
End Sub
[/code[

 

[bran]

That works great, thanks again

 

[JackDanIce]

You're welcome

 

[paspuggie48]

I love this code as it gives me the option to input any data/word to delete a row. It's brililant.
However, being new to VBA/Macros etc, and being stupid and asking a stupid question, how do you change the code so it will look at all the columns as opposed to just the one in this code?
Also, is it easy to put code in to do a repeat/loop so that it prompts again for another word (I have various "criteria" words I search for in my documents) and when finished with typing the criteria it stops then runs the delete ro function?

Regards
Paul

 

[PANKAJUTEKAR]

Try like this

Sub Del_x()
Dim x
x = InputBox("Enter criterion")
With Columns("W")
    .AutoFilter field:=1, Criteria1:=x
    .Resize(Rows.Count - 1).Offset(1).EntireRow.Delete
End With
ActiveSheet.AutoFilterMode = False
End Sub

===============

Vog sir,

I read this query, i tryed this, i got input msg box also.
But i didnt get in which scenario this will use?

If u have time, then pls answer...or require ur kind suggestion.

 

[VoG]

It deletes rows where the value in column W is the same as the value that you enter in the input box.