Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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". |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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". |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows that dont match values in named range
4 letter abbreviations for names.
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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". |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy rows that do not match values in dynamic range. | Excel Programming | |||
Named range scope question (using application.match) | Excel Programming | |||
Locking a couple rows so when you do a delete shift row they dont | Excel Discussion (Misc queries) | |||
how do i delete the extra empty rows and columns that i dont need. | Excel Discussion (Misc queries) | |||
Delete Rows if Certain Values are listed in a Range Name | Excel Programming |