Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
clear range of cells if another becomes blank | Excel Worksheet Functions | |||
Automatically clear values from a range of selected cells | Excel Discussion (Misc queries) | |||
Counting empty cells within a range of cells | New Users to Excel | |||
Counting empty cells within a range of cells | New Users to Excel | |||
Clear range of cells in different worksheet | Excel Programming |