For Each Row in Named Range

W

wpiet

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,
 
C

Chip Pearson

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)
 
B

Bob Phillips

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
 
B

Bernie Deitrick

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
 
P

p45cal

wpiet;459612 said:
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,
Click to expand...

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.
 
Top