For Each Row in Named Range

  • Thread starter Thread starter wpiet
  • Start date Start date
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,
 
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)
 
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
 
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
 
Back
Top