Automatic Deletion of Rows
 

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

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.
-
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"ai18ma" wrote in message
...
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

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" wrote:

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.
-
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"ai18ma" wrote in message
...
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

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)

--
Cheers
JulieD
PS what part of germany, i spent 7 months living & working in Dusseldorf
half a life time ago


"ai18ma" wrote in message
...
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" wrote:

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.
-
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"ai18ma" wrote in message
...
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

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" wrote:

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)

--
Cheers
JulieD
PS what part of germany, i spent 7 months living & working in Dusseldorf
half a life time ago


"ai18ma" wrote in message
...
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" wrote:

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.
-
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"ai18ma" wrote in message
...
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

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.

--
Cheers
JulieD

"ai18ma" wrote in message
...
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" wrote:

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)

--
Cheers
JulieD
PS what part of germany, i spent 7 months living & working in Dusseldorf
half a life time ago


"ai18ma" wrote in message
...
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" wrote:

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.
-
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"ai18ma" wrote in message
...
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

Automatic Deletion of Rows
 

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.

"JulieD" wrote:

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.
-
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"ai18ma" wrote in message
...
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