empty a entire row

[bill gras]

I have formulas in cells in column "C" and when a formula returns a "0" (zero)
I need to empty (not delete) that entire row or rows what ever the case may
be,
from column "C,D,E,F" ect. on wards as far as it will go.
I need to have the rows in columns "A,B" untouched) there are 8000 rows
Can some one help me please
Thanks
regards bill
--
bill gras

[Jim Thomlinson]

Here is some code for you. Right click on the appropriate sheet tab and
select view code. Paste this in the code window and you should be good to
go...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
With Target
If .Column = 3 And .Value = 0 Then
Application.EnableEvents = False
Range(.Offset(0, 1), .Offset(0, Columns.Count - 3)).ClearContents
End If
End With
ErrorHandler:
Application.EnableEvents = True
End Sub

--
HTH...

Jim Thomlinson


"bill gras" wrote:

> I have formulas in cells in column "C" and when a formula returns a "0" (zero)
> I need to empty (not delete) that entire row or rows what ever the case may
> be,
> from column "C,D,E,F" ect. on wards as far as it will go.
> I need to have the rows in columns "A,B" untouched) there are 8000 rows
> Can some one help me please
> Thanks
> regards bill
> --
> bill gras

[bill gras]

Hi Jim
Thank you for your reply and the explanation how to apply the code
Thanks again
regards bill
--
bill gras


"Jim Thomlinson" wrote:

> Here is some code for you. Right click on the appropriate sheet tab and
> select view code. Paste this in the code window and you should be good to
> go...
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error GoTo ErrorHandler
> With Target
> If .Column = 3 And .Value = 0 Then
> Application.EnableEvents = False
> Range(.Offset(0, 1), .Offset(0, Columns.Count - 3)).ClearContents
> End If
> End With
> ErrorHandler:
> Application.EnableEvents = True
> End Sub
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "bill gras" wrote:
>
> > I have formulas in cells in column "C" and when a formula returns a "0" (zero)
> > I need to empty (not delete) that entire row or rows what ever the case may
> > be,
> > from column "C,D,E,F" ect. on wards as far as it will go.
> > I need to have the rows in columns "A,B" untouched) there are 8000 rows
> > Can some one help me please
> > Thanks
> > regards bill
> > --
> > bill gras

[bill gras]

Hi Jim
I have enterd the macro as you adviced , but nothing happend
when I ran the macro
regards bill
--
bill gras


"Jim Thomlinson" wrote:

> Here is some code for you. Right click on the appropriate sheet tab and
> select view code. Paste this in the code window and you should be good to
> go...
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error GoTo ErrorHandler
> With Target
> If .Column = 3 And .Value = 0 Then
> Application.EnableEvents = False
> Range(.Offset(0, 1), .Offset(0, Columns.Count - 3)).ClearContents
> End If
> End With
> ErrorHandler:
> Application.EnableEvents = True
> End Sub
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "bill gras" wrote:
>
> > I have formulas in cells in column "C" and when a formula returns a "0" (zero)
> > I need to empty (not delete) that entire row or rows what ever the case may
> > be,
> > from column "C,D,E,F" ect. on wards as far as it will go.
> > I need to have the rows in columns "A,B" untouched) there are 8000 rows
> > Can some one help me please
> > Thanks
> > regards bill
> > --
> > bill gras

[Norman Jones]

Hi Bill,

Jim's code represents an event procedure. Event procedures are triggered by
an event and do not need to be called or run.

For more information on event procedures, see Chip Pearson at:

http://www.cpearson.com/excel/events.htm

Jim's code ran without problem for me and cleared the required ranges in
response to zero values in column C.

However, as your zero values occur as the result of formulas, I think that
you will need to use the Calculate event. Try, therefore, pasting the
following code into the sheet module:

'===============>>
Private Sub Worksheet_Calculate()
Dim Rng As Range, RngZero As Range
Dim fAddress As String

Application.EnableEvents = False

On Error Resume Next
Set Rng = Range("C1:C8000").SpecialCells(xlFormulas)
On Error GoTo 0

If Not Rng Is Nothing Then

With Rng
Set RngZero = Rng.Find(What:=0, _
After:=Rng(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext)

If Not RngZero Is Nothing Then
fAddress = RngZero.Address

Do
RngZero.Offset(, 1).Resize(1, _
Columns.Count - 3).ClearContents
Set RngZero = .FindNext(RngZero)
Loop While Not RngZero Is Nothing _
And RngZero.Address <> fAddress
End If
End With
End If

Application.EnableEvents = True

End Sub
'<<===============

As with Jim's code, this is worksheet event code and should be pasted into
the worksheets's code module (not a standard module and not the workbook's
ThisWorkbook module):

*******************************************
Right-click the worksheet's tab
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.
*******************************************

---
Regards,
Norman



"bill gras" <[email protected]> wrote in message
news:[email protected]...
> Hi Jim
> I have enterd the macro as you adviced , but nothing happend
> when I ran the macro
> regards bill
> --
> bill gras
>
>
> "Jim Thomlinson" wrote:
>
>> Here is some code for you. Right click on the appropriate sheet tab and
>> select view code. Paste this in the code window and you should be good to
>> go...
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> On Error GoTo ErrorHandler
>> With Target
>> If .Column = 3 And .Value = 0 Then
>> Application.EnableEvents = False
>> Range(.Offset(0, 1), .Offset(0, Columns.Count - 3)).ClearContents
>> End If
>> End With
>> ErrorHandler:
>> Application.EnableEvents = True
>> End Sub
>>
>> --
>> HTH...
>>
>> Jim Thomlinson
>>
>>
>> "bill gras" wrote:
>>
>> > I have formulas in cells in column "C" and when a formula returns a "0"
>> > (zero)
>> > I need to empty (not delete) that entire row or rows what ever the case
>> > may
>> > be,
>> > from column "C,D,E,F" ect. on wards as far as it will go.
>> > I need to have the rows in columns "A,B" untouched) there are 8000
>> > rows
>> > Can some one help me please
>> > Thanks
>> > regards bill
>> > --
>> > bill gras

[bill gras]

Hi Norman
Thankyou for your reply , I have just pasted the code and it
works perfectly.

regards bill
--
bill gras


"Norman Jones" wrote:

> Hi Bill,
>
> Jim's code represents an event procedure. Event procedures are triggered by
> an event and do not need to be called or run.
>
> For more information on event procedures, see Chip Pearson at:
>
> http://www.cpearson.com/excel/events.htm
>
> Jim's code ran without problem for me and cleared the required ranges in
> response to zero values in column C.
>
> However, as your zero values occur as the result of formulas, I think that
> you will need to use the Calculate event. Try, therefore, pasting the
> following code into the sheet module:
>
> '===============>>
> Private Sub Worksheet_Calculate()
> Dim Rng As Range, RngZero As Range
> Dim fAddress As String
>
> Application.EnableEvents = False
>
> On Error Resume Next
> Set Rng = Range("C1:C8000").SpecialCells(xlFormulas)
> On Error GoTo 0
>
> If Not Rng Is Nothing Then
>
> With Rng
> Set RngZero = Rng.Find(What:=0, _
> After:=Rng(1), _
> LookIn:=xlValues, _
> LookAt:=xlWhole, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlNext)
>
> If Not RngZero Is Nothing Then
> fAddress = RngZero.Address
>
> Do
> RngZero.Offset(, 1).Resize(1, _
> Columns.Count - 3).ClearContents
> Set RngZero = .FindNext(RngZero)
> Loop While Not RngZero Is Nothing _
> And RngZero.Address <> fAddress
> End If
> End With
> End If
>
> Application.EnableEvents = True
>
> End Sub
> '<<===============
>
> As with Jim's code, this is worksheet event code and should be pasted into
> the worksheets's code module (not a standard module and not the workbook's
> ThisWorkbook module):
>
> *******************************************
> Right-click the worksheet's tab
> Select 'View Code' from the menu and paste the code.
> Alt-F11 to return to Excel.
> *******************************************
>
> ---
> Regards,
> Norman
>
>
>
> "bill gras" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Jim
> > I have enterd the macro as you adviced , but nothing happend
> > when I ran the macro
> > regards bill
> > --
> > bill gras
> >
> >
> > "Jim Thomlinson" wrote:
> >
> >> Here is some code for you. Right click on the appropriate sheet tab and
> >> select view code. Paste this in the code window and you should be good to
> >> go...
> >>
> >> Private Sub Worksheet_Change(ByVal Target As Range)
> >> On Error GoTo ErrorHandler
> >> With Target
> >> If .Column = 3 And .Value = 0 Then
> >> Application.EnableEvents = False
> >> Range(.Offset(0, 1), .Offset(0, Columns.Count - 3)).ClearContents
> >> End If
> >> End With
> >> ErrorHandler:
> >> Application.EnableEvents = True
> >> End Sub
> >>
> >> --
> >> HTH...
> >>
> >> Jim Thomlinson
> >>
> >>
> >> "bill gras" wrote:
> >>
> >> > I have formulas in cells in column "C" and when a formula returns a "0"
> >> > (zero)
> >> > I need to empty (not delete) that entire row or rows what ever the case
> >> > may
> >> > be,
> >> > from column "C,D,E,F" ect. on wards as far as it will go.
> >> > I need to have the rows in columns "A,B" untouched) there are 8000
> >> > rows
> >> > Can some one help me please
> >> > Thanks
> >> > regards bill
> >> > --
> >> > bill gras
>
>
>