Case statement

[wildturkey]

I have the following code which should look down a sheet and delete all rows where either Course or Consultancy appear in column B, however it doesnt Any ideas please........

Range("A65000").Select
Selection.End(xlUp).Select

xlastrow = ActiveCell.Row

Range("b1").Select

For i = 1 To xlastrow

xtype = ActiveCell.Value

Select Case xtype

Case "Course"
ActiveCell.Rows("1:1").EntireRow.Select
Selection.EntireRow.Delete

Case "Consultancy"
ActiveCell.Rows("1:1").EntireRow.Select
Selection.EntireRow.Delete

End Select

ActiveCell.Offset(1, 0).Select


Next i

 

[Richard Schollar]

Hi

If you want to do it like this then you need to loop thru the range backwards ie from xLastRow to 1 Step -1 (as otherwise the numbering gets stuffed up when you have deleted a row).

There are easier ways however, such as using autofilter.

 

[wildturkey]

Many thanks, went round the trees a few times more and have now got it.

 

[Jon von der Heyden]

As richard has suggested Auto Filter is an easier way and when performed by a macro it typically works quicker. See this post here if you are interested in the auto filter method: http://www.mrexcel.com/board2/viewtopic.php?t=192430

 

[wildturkey]

Id had problems with auto filter in the past having large worksheets and not knowing the last row, or how many rows I would need to delete. This way just seams to be a little neater.

However unless I can find away in vba to identify a cell that contains the letter x then auto filter will have to be used.

Cheers Both

 

[Richard Schollar]

However unless I can find away in vba to identify a cell that contains the letter x then auto filter will have to be used.

Cheers Both

Sub test()
If InStr(1, Range("A1").Value, "x", vbTextCompare) > 0 Then
    'do something
End If
End Sub

 

[wildturkey]

Thanks both,

Ive just got the autofilter working nicely including the delete and that formula for containing x will come in handy!!

Cheers