Clear cells range if certain cells are all empty
If you really mean just clear the contents then perhaps try this:-
Option Explicit
Sub Clearme()
Dim LastRw As Long
Dim RngChk As Range
Dim RngClr As Range
Dim x As Long
LastRw = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For x = 1 To LastRw
Set RngChk = Cells(x, "B").Resize(1, 4)
If Application.WorksheetFunction.CountA(RngChk) = 0 Then
Set RngClr = _
Union(Cells(x, "A"), Range("F" & x).Resize(1, 3), Cells(x,
"J"))
RngClr.ClearContents
End If
Next x
End Sub
Will delete data in Col A, Cols F:H, Col J - Edit as necessary.
Did you really mean just clear though, or did you mean delete the row?
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03
----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
"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?
|