Selective Deleting
 

Hi,
I have an excel template that includes all the possible parameters of
projects in that specific field.
The parameters are listed, each in a different row, with 2 columns. One with
the parameter title and the other for its cost.

Now, different projects may use most or all (but not more) parameters in
this list. So i am trying to write a macro that at the end, will scan the
document and find all the rows in column 2 (cost) that has Zero in it and
delete the entire row. Thus leaving behind only the parameters i used.

thanks

Selective Deleting
 

Sub DeleteZeros()
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngFirst As Range
Dim rngCurrent As Range
Dim rngFound As Range

Set wks = Sheets("Sheet1") 'Change to suit
Set rngToSearch = wks.Columns("B") 'Change to suit
Set rngCurrent = rngToSearch.Find(0, , xlValues, xlWhole)
If rngCurrent Is Nothing Then
MsgBox "Nothing Found"
Else
Set rngFound = rngCurrent
Set rngFirst = rngCurrent
Do
Set rngFound = Union(rngCurrent, rngFound)
Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngCurrent.Address = rngFirst.Address
rngFound.Delete
End If
End Sub
--
HTH...

Jim Thomlinson


"Hesham" wrote:

Hi,
I have an excel template that includes all the possible parameters of
projects in that specific field.
The parameters are listed, each in a different row, with 2 columns. One with
the parameter title and the other for its cost.

Now, different projects may use most or all (but not more) parameters in
this list. So i am trying to write a macro that at the end, will scan the
document and find all the rows in column 2 (cost) that has Zero in it and
delete the entire row. Thus leaving behind only the parameters i used.

thanks

Selective Deleting
 

Sorry one mistake in this

Sub DeleteZeros()
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngFirst As Range
Dim rngCurrent As Range
Dim rngFound As Range

Set wks = Sheets("Sheet1") 'Change to suit
Set rngToSearch = wks.Columns("B") 'Change to suit
Set rngCurrent = rngToSearch.Find(0, , xlValues, xlWhole)
If rngCurrent Is Nothing Then
MsgBox "Nothing Found"
Else
Set rngFound = rngCurrent
Set rngFirst = rngCurrent
Do
Set rngFound = Union(rngCurrent, rngFound)
Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngCurrent.Address = rngFirst.Address
rngFound.EntireRow.Delete 'oops...
End If
End Sub

--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

Sub DeleteZeros()
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngFirst As Range
Dim rngCurrent As Range
Dim rngFound As Range

Set wks = Sheets("Sheet1") 'Change to suit
Set rngToSearch = wks.Columns("B") 'Change to suit
Set rngCurrent = rngToSearch.Find(0, , xlValues, xlWhole)
If rngCurrent Is Nothing Then
MsgBox "Nothing Found"
Else
Set rngFound = rngCurrent
Set rngFirst = rngCurrent
Do
Set rngFound = Union(rngCurrent, rngFound)
Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngCurrent.Address = rngFirst.Address
rngFound.Delete
End If
End Sub
--
HTH...

Jim Thomlinson


"Hesham" wrote:

Hi,
I have an excel template that includes all the possible parameters of
projects in that specific field.
The parameters are listed, each in a different row, with 2 columns. One with
the parameter title and the other for its cost.

Now, different projects may use most or all (but not more) parameters in
this list. So i am trying to write a macro that at the end, will scan the
document and find all the rows in column 2 (cost) that has Zero in it and
delete the entire row. Thus leaving behind only the parameters i used.

thanks

Selective Deleting
 

Hi Hesham

See this page for a few options
http://www.rondebruin.nl/delete.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Hesham" wrote in message ...
Hi,
I have an excel template that includes all the possible parameters of
projects in that specific field.
The parameters are listed, each in a different row, with 2 columns. One with
the parameter title and the other for its cost.

Now, different projects may use most or all (but not more) parameters in
this list. So i am trying to write a macro that at the end, will scan the
document and find all the rows in column 2 (cost) that has Zero in it and
delete the entire row. Thus leaving behind only the parameters i used.

thanks