delete rows that dont match values in named range

  • Thread starter Thread starter J.W. Aldridge
  • Start date Start date
J

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".
 
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
 
getting debug for "type mismatch" error on the following line...

If Cells(rn, "B").Value <> Text Then
 
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
 
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.
 
tried...

Type mismatch... This portion was highlighted.

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