Clear cells range if certain cells are all empty
Sub test()
Dim iLastRow As Long
Dim i As Long
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To cLastRow
If Application.CountA(Cells(i, "B").Resize(, 4)) = 0 Then
Cells(i, "A").Resize(, 5).ClearContents
End If
Next i
End Sub
--
HTH
RP
(remove nothere from the email address if mailing direct)
"gschimek - ExcelForums.com"
wrote in message ...
This may be difficult to follow, but let me try to explain my
question. I'm trying to create a macro that will scan 4 cells in a
row, and if it finds all of those cells are empty, it should clear
the contents of those 4 cells, along with some other adjacent cells
in that same row. It should then go down the spreadsheet and do the
same check for subsequent rows until it reaches the end.
To illustrate:
A1 has a product name, B1-E1 may or may not have data about that
product. If B1-E1 are all blank, it should clear the contents of
A1-E1. After that, it should move on to row 2 and check B2-E2.
Is there a way to do this with VBA or some built-in Excel function?
|