dont need anymore these information

[Kel]

Hi all and thanx again for sharing your great knowledge with others,

Im looking to do the following it should be as a macro in VB on a sheet
which is protected and with a password,

if say AND(D1 is less then zero (0) delete contents from entire row,
D2 less then zero delete contents from entire row, and same on D3 and so on,

so the macro will search the entire sheet in column D, if cell in D column
is less then 0 delete contents from entire row.

Thanx again

[bigwheel]

This will remove the entire row when the cell being checked is less than zero
until the cell is found to be empty. Is this what you want or do you just
want the row to be blanked?

Range("D1").Select
Do While Not IsEmpty(ActiveCell)
If ActiveCell < 0 Then
Selection.EntireRow.Delete
End If
ActiveCell.Offset(1, 0).Select
Loop

"Kel" wrote:

> Hi all and thanx again for sharing your great knowledge with others,
>
> Im looking to do the following it should be as a macro in VB on a sheet
> which is protected and with a password,
>
> if say AND(D1 is less then zero (0) delete contents from entire row,
> D2 less then zero delete contents from entire row, and same on D3 and so on,
>
> so the macro will search the entire sheet in column D, if cell in D column
> is less then 0 delete contents from entire row.
>
> Thanx again

[JMB]

I think you will have a problem if there are two consecutive negative
numbers. Once the entire row is deleted, Excel will move to the next line
and your code will then move to the next line, causing a row to be skipped.
Maybe find the last row containing data and work from the bottom up.

I usually set a range variable for the rows I want deleted, then delete all
of them at the end of the code (another option)

Sub DeleteNegatives()
Dim x As Object
Dim RangeToDelete As Range

For Each x In Application.Intersect(Columns(4).EntireColumn, _
ActiveSheet.UsedRange)
If x.Value < 0 Then
If RangeToDelete Is Nothing Then
Set RangeToDelete = x
Else: Set RangeToDelete = Union(RangeToDelete, x)
End If
End If
Next x

If Not RangeToDelete Is Nothing Then _
RangeToDelete.EntireRow.Delete

End Sub

"bigwheel" wrote:

> This will remove the entire row when the cell being checked is less than zero
> until the cell is found to be empty. Is this what you want or do you just
> want the row to be blanked?
>
> Range("D1").Select
> Do While Not IsEmpty(ActiveCell)
> If ActiveCell < 0 Then
> Selection.EntireRow.Delete
> End If
> ActiveCell.Offset(1, 0).Select
> Loop
>
> "Kel" wrote:
>
> > Hi all and thanx again for sharing your great knowledge with others,
> >
> > Im looking to do the following it should be as a macro in VB on a sheet
> > which is protected and with a password,
> >
> > if say AND(D1 is less then zero (0) delete contents from entire row,
> > D2 less then zero delete contents from entire row, and same on D3 and so on,
> >
> > so the macro will search the entire sheet in column D, if cell in D column
> > is less then 0 delete contents from entire row.
> >
> > Thanx again

[JMB]

Whatever you go with, you will need code at the beginning to unprotect the
sheet, then code at the end to protect the sheet again (or you could do these
steps yourself before and after running the macro).

ActiveSheet.Unprotect Password:="password"

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True, Password := "password"



"Kel" wrote:

> Hi all and thanx again for sharing your great knowledge with others,
>
> Im looking to do the following it should be as a macro in VB on a sheet
> which is protected and with a password,
>
> if say AND(D1 is less then zero (0) delete contents from entire row,
> D2 less then zero delete contents from entire row, and same on D3 and so on,
>
> so the macro will search the entire sheet in column D, if cell in D column
> is less then 0 delete contents from entire row.
>
> Thanx again