For each....that is not blank

[Aria]

Hello,
How do you change this line to become For Each c in the range of B2 to
B12 that is Not Blank?

For Each c In Sheets("Inventory").Range("B2:B12")

Aria

*** Sent via Developersdex http://www.developersdex.com ***

 

[Excelenator]

Code:
--------------------
For Each c In Sheets("Inventory").Range("B2:B12")
If c.value <> "" Then
-your code here-
End if
Next c

 

[Dave Peterson]

How about just ignore the blanks, but still loop through them:

for each c in sheets("inventory").range("B2:B12").cells
if c.value = "" then
'do nothing, skip it
else
'do the real work here
end if
next c

 

[Bernie Deitrick]

If you know that they are constants (but not "" - those aren't actually blank), you could use:
For Each c In Sheets("Inventory").Range("B2:B12").SpecialCells(xlCellTypeConstants,23)

Or if they are all formulas (but not formulas that return "" - those also aren't actually blank):
For Each c In Sheets("Inventory").Range("B2:B12").SpecialCells(xlCellTypeFormulas,23)

HTH,
Bernie
MS Excel MVP

 

[Aria]

Thanks Excelenator, that's exactly what I needed. Thanks to Dave also.
How do you set another condition that for each d in C2 to C12 that
matches to $A$1, then afterwards trigger this code?

For Each c In Sheets("Inventory").Range("B2:B12")
If c.value <> "" Then
-your code here-
End if
Next c

Aria


*** Sent via Developersdex http://www.developersdex.com ***

 

[Excelenator]

Something like this???



Code:
--------------------
For Each d in Sheets("Inventory").Range("C2:C12")
IF d.value = Range("A1").Value Then
For Each c In Sheets("Inventory").Range("B2:B12")
If c.value <> "" Then
-your code here-
End if
Next c
Next d

 

[Aria]

Yes, thank-you Excelenator! That's exactly what I needed.
Aria



*** Sent via Developersdex http://www.developersdex.com ***