delete rows that dont match values in named range
 

Header row a5:F5.
Data starts at A6..
Based on values starting in column B6 to the bottom, I would like to
delete each row that doesnt match the values in named range "IDS".

delete rows that dont match values in named range
 

text = range("IDS").value
for rn = range("A6").End(xlDown).Row to 6 step -1
if cells(rn,"B").Value < text then
rows(rn).Delete
end if
next



"J.W. Aldridge" wrote in message
...
Header row a5:F5.
Data starts at A6..
Based on values starting in column B6 to the bottom, I would like to
delete each row that doesnt match the values in named range "IDS".

delete rows that dont match values in named range
 

getting debug for "type mismatch" error on the following line...

If Cells(rn, "B").Value < Text Then

delete rows that dont match values in named range
 

what values are in variables rn and Text ?

"J.W. Aldridge" wrote in message
...
getting debug for "type mismatch" error on the following line...

If Cells(rn, "B").Value < Text Then

delete rows that dont match values in named range
 

4 letter abbreviations for names.

delete rows that dont match values in named range
 

rn should be a row number
use F8 to step through the code and at the line you mention, hover the mouse
over rn and text to see what values they hold

rn is
DIM rn as Long

and its used in the for...next loop


for rn = range("A6").End(xlDown).Row to 6 step -1


"Patrick Molloy" wrote in message
...
what values are in variables rn and Text ?

"J.W. Aldridge" wrote in message
...
getting debug for "type mismatch" error on the following line...

If Cells(rn, "B").Value < Text Then

delete rows that dont match values in named range
 

Hovered over text line and nothing came up.
So, i went back to

Text = Range("IDS").Value

and this showed up.

Text = Range("IDS").Value = <text mismatch

delete rows that dont match values in named range
 

Here is a macro for you to try...

Sub DeleteRows()
Dim X As Long
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For X = LastRow To 6 Step -1
If .Cells(X, "B").Value < Application.Names("IDS"). _
RefersToRange Then .Cells(X, "B").EntireRow.Delete
Next
End With
End Sub

As set up, it assumes that the active sheet is the sheet you want to perform
your deletions on. If that will not always be the case, then change this
line...

With ActiveSheet

to this...

With Worksheets("Sheet1")

where you would change Sheet1 to name of the actual sheet you will want to
do the deletions on.

--
Rick (MVP - Excel)


"J.W. Aldridge" wrote in message
...
Header row a5:F5.
Data starts at A6..
Based on values starting in column B6 to the bottom, I would like to
delete each row that doesnt match the values in named range "IDS".

delete rows that dont match values in named range
 

tried...

Type mismatch... This portion was highlighted.

If .Cells(X, "B").Value < Application.Names("IDS"). _
RefersToRange Then