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]

Please Login or Register  to view this content.

[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


Aria wrote:
>
> 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 ***

--

Dave Peterson

[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> wrote in message news:[email protected]...
> 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 ***

[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???

Please Login or Register  to view this content.

[Aria]

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



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