fredlo2010
06-29-2012, 06:02 PM
Hello guys,
I have this code that the experts in this forum helped me with. Here is the link http://www.vbaexpress.com/forum/showthread.php?t=42325
Basically the code goes through column B and if there is any blank cells then the range A:B for that row will be deleted as long as the contents in cell A are not "Cookies", "Salt", ""
The problem is that this code goes way beyond my limited VBA knowledge. I cannot modify it I can barely read it.
I want to modify so if there is more than two consecutive "Cookies", "Salt", "" then delete that range too. Sometimes I end up with data looking like this:
Cookies
Cookies
Cookies
Cookies
4525
Salt
Cookies
425
55886
Also i would like to see if I can extend my search to a different cell range. Look for empty cells in column B and C, if found the delete the range A:C for that specific row.
Here is the code I am using.
Sub test()
Dim a, b(), i As Long, ii As Long, n As Long, flg As Boolean
With Range("a2", Range("a" & Rows.Count).End(xlUp)).Resize(, 2)
a = .Value
Redim b(1 To UBound(a, 1), 1 To UBound(a, 2))
For i = 1 To UBound(a, 1)
If a(i, 2) = "" Then
Select Case a(i, 1)
Case "Cookies", "Salt", ""
n = n + 1
flg = True
Case Else
End Select
Else
n = n + 1: flg = True
End If
If flg Then
For ii = 1 To UBound(a, 2)
b(n, ii) = a(i, ii)
Next
End If
flg = False
Next
.Value = b
End With
End Sub
Thanks a lot for your help guys
I have this code that the experts in this forum helped me with. Here is the link http://www.vbaexpress.com/forum/showthread.php?t=42325
Basically the code goes through column B and if there is any blank cells then the range A:B for that row will be deleted as long as the contents in cell A are not "Cookies", "Salt", ""
The problem is that this code goes way beyond my limited VBA knowledge. I cannot modify it I can barely read it.
I want to modify so if there is more than two consecutive "Cookies", "Salt", "" then delete that range too. Sometimes I end up with data looking like this:
Cookies
Cookies
Cookies
Cookies
4525
Salt
Cookies
425
55886
Also i would like to see if I can extend my search to a different cell range. Look for empty cells in column B and C, if found the delete the range A:C for that specific row.
Here is the code I am using.
Sub test()
Dim a, b(), i As Long, ii As Long, n As Long, flg As Boolean
With Range("a2", Range("a" & Rows.Count).End(xlUp)).Resize(, 2)
a = .Value
Redim b(1 To UBound(a, 1), 1 To UBound(a, 2))
For i = 1 To UBound(a, 1)
If a(i, 2) = "" Then
Select Case a(i, 1)
Case "Cookies", "Salt", ""
n = n + 1
flg = True
Case Else
End Select
Else
n = n + 1: flg = True
End If
If flg Then
For ii = 1 To UBound(a, 2)
b(n, ii) = a(i, ii)
Next
End If
flg = False
Next
.Value = b
End With
End Sub
Thanks a lot for your help guys