empty a entire row

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Hi Jim
Thank you for your reply and the explanation how to apply the code
Thanks again
regards bill
 
Hi Jim
I have enterd the macro as you adviced , but nothing happend
when I ran the macro
regards bill
 
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.
*******************************************
 
Hi Norman
Thankyou for your reply , I have just pasted the code and it
works perfectly.

regards bill
 
Back
Top