Clear cells range if certain cells are all empty
 

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?

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?

Clear cells range if certain cells are all empty
 

Typo

Sub test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
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)


"Bob Phillips" wrote in message
...
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?

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?

Clear cells range if certain cells are all empty
 

Hi Bob, may be a redundant resize in there assuming the condition that
triggers it is true though :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Bob Phillips" wrote in message
...
Typo

Sub test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
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)


"Bob Phillips" wrote in message
...
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?

Clear cells range if certain cells are all empty
 

"Ken Wright" wrote in message
...
Hi Bob, may be a redundant resize in there assuming the condition that
triggers it is true though :-)

LOL and blush!

Clear cells range if certain cells are all empty
 

rotflmao ( Admits to nearly doing the exact same thing <g )

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Bob Phillips" wrote in message
...

"Ken Wright" wrote in message
...
Hi Bob, may be a redundant resize in there assuming the condition that
triggers it is true though :-)

LOL and blush!