Automatic Deletion of Rows

[ai18ma]

Dear Experts,

I wish to :
1. Select columns containing a certain value ( here 0)
2. Then delete the ROWs with that valie automatically.

I found the command for automatic search, but how to delete? Thank you.

Best Regards

 

[JulieD]

Hi

this code will look for a value in column A and delete the rows that the
value appears in
---
Sub DelRows()
Dim c As Range

strfind = InputBox("enter value to find and delete")

With Worksheets(1).Range("A:A")
Do
Set c = .Find(strfind, LookIn:=xlValues, lookat:=xlPart, _
MatchCase:=False)
If c Is Nothing Then Exit Do
c.EntireRow.Delete
Loop
End With
End Sub
---
to use the code, right mouse click on a sheet tab, choose view code, this
will display the vbe window, choose insert / module from the menu and then
copy & paste this code on the right hand side of the screen - if you want
the code to work on columns other than A, change the Range("A:A") to the
appropriate columns. Then use ALT & F11 to switch back to your workbook,
choose tools / macro / macros, find DelRows and press RUN

Note: it is always a good idea to try something new on a copy of your
workbook first ... just to ensure that the results are what you want.
-

 

[ai18ma]

Dear JulieD

Many thanks, it works! However, it couldn't resolve "inputbox", maybe
because I am using a German version of Excel. I just replaced strfind with
the value to search for, and it did it! Vielen herzlichen Dank!

 

[JulieD]

you're welcome - glad it works ... not sure what the German equivalent to
INPUTBOX is ... luckily i only have to work in English - i think Excel would
be 1000 times harder if i had to deal with language differences (i have
enought trouble remembering to spell things the "american" way)

 

[ai18ma]

I live in Cologne, about 30 miles south of Duesseldorf, a very nice place!
How did you like it here? Kinda hard getting along with Germans, ey?!

Regards

 

[JulieD]

actually i didn't have much problem at all - would have chosen to live in
germany if i didn't end up back in australia.

 

[x10sion]

Thank you JulieD the code came very handy!!
:*

can you pl. modify it so that it can search with wild cards too..

Thanks in advance.