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

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" <(E-Mail Removed)> wrote in message
news:269e5d30-64f0-4fd8-9e76-(E-Mail Removed)...
> 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".

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

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

what values are in variables rn and Text ?

"J.W. Aldridge" <(E-Mail Removed)> wrote in message
news:85575576-1dda-4de0-b560-(E-Mail Removed)...
> getting debug for "type mismatch" error on the following line...
>
> If Cells(rn, "B").Value <> Text Then

4 letter abbreviations for names.

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" <(E-Mail Removed)> wrote in message
news:37F4AD6D-CFD9-492D-B603-(E-Mail Removed)...
> what values are in variables rn and Text ?
>
> "J.W. Aldridge" <(E-Mail Removed)> wrote in message
> news:85575576-1dda-4de0-b560-(E-Mail Removed)...
>> getting debug for "type mismatch" error on the following line...
>>
>> If Cells(rn, "B").Value <> Text Then
>

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>

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" <(E-Mail Removed)> wrote in message
news:269e5d30-64f0-4fd8-9e76-(E-Mail Removed)...
> 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".

tried...

Type mismatch... This portion was highlighted.

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