ClearContents with condition

R

Richard

Hello,

Using Excel XP.

I have the following range in Sheet1:

Range E1: H downwards
(b) = Blank cell

DESC CODE PURCHASE1 PURCHASE2
Widget S101 (b) $100.00
Widget S102 (b) (b)
Widget S103 $250.00 (b)
Widget S104 (b) (b)
Widget S105 $325.00 (b)

I am trying to devise a macro to clear the description
cell (column E) and the code cell (column F) if both
cells in columns G and H are blank.

So, I end up with:
DESC CODE PURCHASE1 PURCHASE2
Widget S101 (b) $100.00
(b) (b) (b) (b)
Widget S103 $250.00 (b)
(b) (b) (b) (b)
Widget S105 $325.00 (b)

The following macro does not work:

Sub myDelete()
Dim ws As Worksheet
Dim rngG As Range
Dim rngH As Range
Dim rngZ As Range

Set ws = Worksheets("Sheet1")
Set rngG = ws.Range(Range("G2"), Range("G65536").End
(xlUp))
Set rngH = ws.Range(Range("H2"), Range("H65536").End
(xlUp))
Set rngZ = Intersect(rngG, rngH)

With ws
If rngZ Is Nothing Then
rngZ.Offset(0, -1).ClearContents
rngZ.Offset(0, -2).ClearContents
End If
End With

End Sub

I will appreciate any assistance.

TIA.

Richard
 
P

Peter

Richard

This does it but leaves gaps, I suppose that these are
used in columns A to D

Sub delDescr()
Dim nr As Long, i As Long
nr = Application.WorksheetFunction.CountA(Range("E:E"))
For i = 1 To nr
If IsEmpty(Cells(i, 7)) And IsEmpty(Cells(i, 8)) Then
Range(Cells(i, 5), Cells(i, 6)).ClearContents
End If
Next
End Sub


Regards
Peter

[email protected]
 
Top