Jim, because you enclose rw in quotes, "rw:rw", you are telling VB to use
the test string rw instead of the variable value of rw. If you did this:
Rows(rw:rw), it still would not work because of the colon not being enclosed
in quotes. This may work: rows(rw & ":" & rw), but the simple way is
rows(rw).
Also, you are deleting top down. This means that if rw is 10 and you delete
row 10, that row 11 became row 10 and doesn't get tested. Instead, delete
from the bottom up by changing your For statement to: For rw = 32767 to 1
step -1 And, you don't have to select a row to delete it. Use
Rows(rw).Delete.
Hope this helps,
Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel
Quote:> Hi all,
> In the following Sub, I get a type mismatch in the line "
> Rows("rw:rw").Select ". I would have thought that if the rw variable (as
an
> integer) worked for the Cells object it should have worked for the rows
> object. When I hold my cursor over the rw in the previous line it says
"rw
> =15" so I don't understand why I am getting the type mismatch. What am I
> doing wrong?
> Thanks for the continued help!
> Jim
> Sub DeleteIMsg()
> Dim rw As Integer
> For rw = 1 To 32767
> If Cells(rw, 8).Value = "" Then Exit Sub
> If Cells(rw, 8).Value = "I" Then
> If Cells(rw, 9).Value = "NI" Then
> Rows("rw:rw").Select
> Selection.Delete shift:=xlUp
> End If
> End If
> Next rw
> End Sub
by