Clearing contents

[Zak]

Hi,

Hope u can help me..

I would like to know how i can tell a macro to clear contents when:

If a cell in column E is NOT blank then clear the contents of that entire
row (but NOT delete the row), and i want to start looking for non blanks
after the header row to avoid the hearder getting cleared.

thanks for your help.

 

[JW]

One way..
Sub Zak()
Dim lRow As Long, r As Long
lRow = Cells.Find(what:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
For r = 2 To lRow
If Not IsEmpty(Range("E" & r)) Then _
Range("E" & r).EntireRow.ClearContents
Next r
End Sub

 

[Per Jessen]

Hi

Look at this:

Sub ClearRow()
Dim TargetRange As Range
Set TargetRange = Range("E2", Range("E65536").End(xlUp))

For Each c In TargetRange
If c.Value <> "" Then c.EntireRow.ClearContents
Next
End Sub

Regards,
Per

 

[Gary''s Student]

Try this:

Sub eclear()
n = Cells(Rows.Count, "E").End(xlUp).Row
For i = 2 To n
If IsEmpty(Cells(i, "E")) Then
Else
Cells(i, "E").EntireRow.Clear
End If
Next
End Sub

 

[Zak]

Thanks guys its worked a treat, appreciate the prompt reply.