loop

[vimalanathk]

hi,

can some pls help with the script which gives

do

if "the cell begins with a particular word"

select the entire row

delete

move to next row

end loop if the row is blank

script ends

regards,
vimala K

[JOHN H. DAVIS]

Please use code tags when posting.

Sub vimalanathk()
Dim i As Long
For i = Range("A" & Rows.count).End(xlUp).Row To 2 Step -1
    If Left(Range("A" & i), 4) = "word" Then Rows(i).Delete
Next i
End Sub

Assumes the particular word is "word" and the values are in Column "A".

[vimalanathk]

do i need to give the entire text of the cell which i want to delete....

i just want to give the starting word of the cell...

by using the above script cursor is not moving from cell a1 and no action is taking place in the work book

regards,
vimala

[stnkynts]

If i could respectfully amend John's code this might make it a little easier for the OP to edit to there desire:

Sub vimalanathk()
Dim i As Long, lCount As Long
Dim str As String

str = "word"  'change your word here
lCount = Len(str)

For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
    If Left(Range("A" & i), lCount) = str Then Rows(i).Delete
Next i

End Sub

Edit: I just noticed that this may be a cross post http://www.excelforum.com/excel-prog...continues.html.

[vimalanathk]

macro is running only between the below lines

If Left(Range("A" & i), lCount) = str Then Rows(i).Delete
Next i



and no rows are getting deleted

[vimalanathk]

Hi,

in the attached excel i want to delete the rows which are starting with location, mcp and last

regards,
vimala

[JOHN H. DAVIS]

Another:

Sub vimalanathk()
Dim i As Long
For i = Range("A" & Rows.count).End(xlUp).Row To 2 Step -1
    Select Case Left(Range("A" & i), 3)
    Case Is = "Las", "MCP", "Loc"
        Rows(i).Delete
    End Select
Next i
End Sub

[stnkynts]

It would have been nice if you would have mentioned multiple words. I am curious how you tried to put the multiple words in to get it to work. Also, you were probably experiencing a "case" issue.

Sub vimalanathk()
Dim i As Long, lCount1 As Long, lCount2 As Long, lCount3 As Long
Dim str1 As String, str2 As String, str3 As String

str1 = "LOCATION"  'change your word here
str2 = "MCP"
str3 = "LAST"
lCount1 = Len(str1)
lCount2 = Len(str2)
lCount3 = Len(str3)

For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
    If UCase(Left(Range("A" & i), lCount1)) = str1 Or UCase(Left(Range("A" & i), lCount2)) = str2 Or UCase(Left(Range("A" & i), lCount3)) = str3 Then
        Rows(i).Delete
    End If
Next i

End Sub