For Each Row in Named Range
 

What is the simplest syntax to use to accomplish the following?:

For Each Row in [NamedRange]
If the value in column B = 1 and the value in column D <= [SomeValue] Then
..
..
..
End If
Next Row

Thanks,
--
Will

For Each Row in Named Range
 

Essentially, you'll want to do something like

Dim R As Range
For Each R In Range("MyRange")
If R.EntireRow.Cells(1, "B").Value = 1 And _
R.EntireRow.Cells(1, "D").Value = SomeValue Then
' do something
End If
Next R


HOWEVER.... if that "do something" is to delete a row, you need to
work from the bottom of the range upwards to the top of the range.
Otherwise you'll end up skipping rows:

For deleting, use

Dim RNdx As Long
With Range("MyRange")
For RNdx = .Cells(.Cells.Count).Row To .Cells(1, 1).Row Step -1
If Cells(RNdx, "B").Value = 1 And _
Cells(1, "D").Value = "SomeValue" Then
Rows(RNdx).Delete
End If
Next RNdx
End With

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Thu, 20 Aug 2009 10:49:01 -0700, wpiet
wrote:

What is the simplest syntax to use to accomplish the following?:

For Each Row in [NamedRange]
If the value in column B = 1 and the value in column D <= [SomeValue] Then
.
.
.
End If
Next Row

Thanks,

For Each Row in Named Range
 

For Each mRow In Range(" NamedRange").Rows

If Cells(mRow.Row, "B").value -= 1 And Cells(mRow.Row, "D") <= somevalue
Then

...
End If
Next mRow

--
__________________________________
HTH

Bob

"wpiet" wrote in message
...
What is the simplest syntax to use to accomplish the following?:

For Each Row in [NamedRange]
If the value in column B = 1 and the value in column D <= [SomeValue]
Then
.
.
.
End If
Next Row

Thanks,
--
Will

For Each Row in Named Range
 

Will,

Dim myRow As Range

For Each myRow In Range("Fred").Rows
If Intersect(Range("B:B"), myRow).Value = 1 And _
Intersect(Range("D:D"), myRow).Value <= 5 Then
MsgBox "Hello from row " & myRow.Row
End If
Next myRow


HTH,
Bernie
MS Excel MVP


"wpiet" wrote in message
...
What is the simplest syntax to use to accomplish the following?:

For Each Row in [NamedRange]
If the value in column B = 1 and the value in column D <= [SomeValue] Then
.
.
.
End If
Next Row

Thanks,
--
Will

For Each Row in Named Range
 

wpiet;459612 Wrote:
What is the simplest syntax to use to accomplish the following?:

For Each Row in [NamedRange]
If the value in column B = 1 and the value in column D <= [SomeValue]
Then
..
..
..
End If
Next Row

Thanks,
--
Will

Assuming th active sheet is the one in question,
either:
For Each cll In Intersect(Range("NamedRange"), Columns("B"))
If cll.Value = 1 And cll.Offset(, 2) <= Somevalue Then
'..
'..
End If
Next cll
orFor Each rw In Range("NamedRange").Rows
If Cells(rw.Row, 2) = 1 And Cells(rw.Row, 4) <= Somevalue Then
'..
'..
End If
Next rw
and lots more..


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=127188