delete rows that dont match values in named range

[J.W. Aldridge]

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

 

[Patrick Molloy]

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]

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

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

 

[Patrick Molloy]

what values are in variables rn and Text ?

 

[J.W. Aldridge]

4 letter abbreviations for names.

 

[Patrick Molloy]

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

 

[J.W. Aldridge]

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>

 

[Rick Rothstein]

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.

 

[J.W. Aldridge]

tried...

Type mismatch... This portion was highlighted.

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