dim problem

[Annette]

Because I am not real skilled at this, I understand what Option Explicit
means and that I have to dim certain pieces of code .. I do not know what to
dim as I'm getting an error for the rw in this code. Can someone help me
with this?
_____________________
set rw = cells(rows.count,"F").End(xlup)

list = Array("DRH1", "DRH2", "DRH3", "DRI1", _

"DRI2","DRI3", "DRIA", "DRIB")

for i = rw to 1 step -1

res = Application.Match(cells(rw,"F").Value,list,0)

if not iserror(res) then

cells(rw,"F").EntireRow.delete

end if

Next

 

[Lonnie M.]

Hi,
Dim rw As Range

HTH--Lonnie M.

 

[Bob Phillips]

And

Dim i As Long
Dim list As Variant
Dim res As Variant

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Annette]

grrrr .. still not working with all this helpful information ... here's the
code with the additives:

Dim i As Long
Dim list As Variant
Dim res As Variant
Dim rw As Range

rw = Cells(Rows.Count, "F").End(xlUp).Row
Set rw = Cells(Rows.Count, "F").End(xlUp)
list = Array("DRH1", "DRH2", "DRH3", "DRI1", _
"DRI2", "DRI3", "DRIA", "DRIB")
For i = rw To 1 Step -1
res = Application.Match(Cells(rw, "F").Value, list, 0)
If Not IsError(res) Then
Cells(rw, "F").EntireRow.Delete
End If
Next

 

[Norman Jones]

Hi Annette,

In addition to Bob's response,

Dim rw As Long

Also, change:

set rw = cells(rows.count,"F").End(xlup)

to

rw = Cells(Rows.Count, "F").End(xlUp).Row

 

[Norman Jones]

Hi Annette,

Try:

Sub Tester()
Dim rw As Long
Dim i As Long
Dim res As Variant
Dim list As Variant

rw = Cells(Rows.Count, "F").End(xlUp).Row

list = Array("DRH1", "DRH2", "DRH3", "DRI1", _
"DRI2", "DRI3", "DRIA", "DRIB")

For i = rw To 1 Step -1
res = Application.Match(Cells(rw, "F").Value, list, 0)
If Not IsError(res) Then
Cells(rw, "F").EntireRow.Delete
End If
Next
End Sub

 

[Annette]

Followed all the suggested and still getting an error so I'm copying with
all the past changes from your suggestions ... I know this can work, just
that I'm too dumb to know how to do:

Sub undistributed()
Dim rw As Long
Dim i As Long
Dim res As Variant
Dim list As Variant
rw = Cells(Rows.Count, "F").End(xlUp).Row
list = Array("DRH1", "DRH2", "DRH3", "DRI1", _
"DRI2", "DRI3", "DRIA", "DRIB")
For i = rw To 1 Step -1
res = Application.Match(Cells(rw, "F").Value, list, 0)
If Not IsError(res) Then
Cells(rw, "F").EntireRow.Delete
End If
Next
End Sub

 

[Annette]

oops ... is not an error .. just doesn't do anything ... no errors .. just
doesn't perform.

 

[Lonnie M.]

Hi Annette, I think you want to replace the rw's with in your loop with
"i":

Sub undistributed()
Dim rw As Long
Dim i As Long
Dim res As Variant
Dim list As Variant
rw = Cells(Rows.Count, "F").End(xlUp).Row
list = Array("DRH1", "DRH2", "DRH3", "DRI1", _
"DRI2", "DRI3", "DRIA", "DRIB")
For i = rw To 1 Step -1
res = Application.Match(Cells(i, "F").Value, list, 0)
If Not IsError(res) Then
Cells(i, "F").EntireRow.Delete
End If
Next
End Sub

HTH--Sorry about giving you the wrong variable type for rw above, I
must have still been dazed from lunch--Lonnie M.

 

[Norman Jones]

Hi Annette,

The last two instances of rw should be i

Try this amended version:

Sub undistributed()
Dim rw As Long
Dim i As Long
Dim res As Variant
Dim list As Variant

With Sheets("Sheet1") '<<======= CHANGE TO SUIT
rw = .Cells(Rows.Count, "F").End(xlUp).Row
list = Array("DRH1", "DRH2", "DRH3", "DRI1", _
"DRI2", "DRI3", "DRIA", "DRIB")
For i = rw To 1 Step -1
res = Application.Match(.Cells(i, "F").Value, list, 0)
If Not IsError(res) Then
.Cells(i, "F").EntireRow.Delete
End If
Next
End With
End Sub


Change Sheet1 to your sheet name.

 

[Alan Beban]

Assuming you've got the right worksheet active, it looks like the Match
function is not finding the value of Cells(rw,"F") in the array; add,
after res=Application.Match etc. the line
MsgBox res
to see what value is in that cell and whether it is a value that is in
the list.

By the way, your loop isn't doing anything except looking for a value is
the same place every iteration--changing the i counter isn't changing
the code that's executed, which is independent of i. Perhaps you meant

Application.Match(Cells(i,"F").Value

Alan Beban

 

[Annette]

Okay .. IT's A WORKING (sorry bout the hollerin) but it is working backwards
.... deleting the wrong ones instead of leaving those, but you know what, I
don't care and I'll work with it as is ... I like ... thanks to everyone who
piped in with great answers!

 

[Lonnie M.]

Annette,
If you want it to delete those that match your list try this:

For i = rw To 1 Step -1
res = Application.Match(Cells(i, "F").Value, list, 0)
If IsError(res) Then
Cells(i, "F").EntireRow.Delete
End If
Next
End

Good Luck!

 

[Lonnie M.]

Annette,
If you want it to delete those that don't match your list try this:


For i = rw To 1 Step -1
res = Application.Match(Cells(i, "F").Value, list, 0)
If IsError(res) Then
Cells(i, "F").EntireRow.Delete
End If
Next
End


Good Luck!

 

[Annette]

EXCELLENT .. 110% better and it works to perfection ... THANKS!