Delete Row Macro Required

[Gambler]

Hi total Newbie here.


I am looking for a macro to delete rows based on the following:


If the words " National Hunt " appear in any cell in the range C3:C700 these rows should be kept i.e. delete rows in the range A3:A700 where " National Hunt " is not in the C column


Thanks In Advance For Any Help Forthcoming.

 

[Mike LH]

Hi,

Try this code.

Sub delete_Me()
Dim LastRow As Long, x As Long
LastRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row
For x = LastRow To 1 Step -1
    If InStr(1, Cells(x, "C").Value, "National Hunt", vbTextCompare) Then
     Rows(x).Delete
    End If
Next
End Sub

 

[Mike LH]

Hi,

Re-reading this I think I got it the wrong way around, try this eversion instead.

Sub delete_Me()
Dim LastRow As Long, x As Long
LastRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row
For x = LastRow To 1 Step -1
    If InStr(1, Cells(x, "C").Value, "National Hunt", vbTextCompare) = 0 Then
     Rows(x).Delete
    End If
Next
End Sub

 

[apo]

Hi..

Not sure if this will work in your scenario.. but one possible way to do it without a loop would be:

* Type "National Hunt" into cell C2 (or C1 if that suits better)..

Sub Delete()
  Columns(3).ColumnDifferences([C2]).EntireRow.Delete
End Sub

 

[Gambler]

Mike LH, thanks for your help, that macro is also what I needed for a second workbook. It deletes rows with " National Hunt " in them.

If you read my initial post, I was looking for a macro TO KEEP ROWS WITH " National Hunt " in them, could you provide a macro to do this please.

Also the macro doesn't really delete if you get my drift (excuse my newbie speak) as I have a total row all the way across the worksheet in row A701 and the macro leaves a big gap between the last actual row of data and the total row A701, I was looking for a macro that would bring the total row directly up with no gap between if you get my drift.

Thanks for your help

 

[Gambler]

apo, thanks for replying but when "National Hunt" is in cell C2 and macro run all rows are deleted

 

[Mike LH]

Mike LH, thanks for your help, that macro is also what I needed for a second workbook. It deletes rows with " National Hunt " in them.

If you read my initial post, I was looking for a macro TO KEEP ROWS WITH " National Hunt " in them, could you provide a macro to do this please.

Also the macro doesn't really delete if you get my drift (excuse my newbie speak) as I have a total row all the way across the worksheet in row A701 and the macro leaves a big gap between the last actual row of data and the total row A701, I was looking for a macro that would bring the total row directly up with no gap between if you get my drift.

Thanks for your help

Hi I realised my error and re-posted #3 above which keeps the rows with national hunt in. The code I posted deletes the entire row so shouldn't leave gaps.

 

[Gambler]

Mike LH, many thanks for sorting me out, very much appreciated, thanks again.

Mike L

 

[Gambler]

Sub delete_Me()
Dim LastRow As Long, x As Long
LastRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row
For x = LastRow To 1 Step -1
If InStr(1, Cells(x, "C").Value, "National Hunt", vbTextCompare) Then
Rows(x).Delete
End If
Next
End Sub

Sorry Mike LH, the above code is great, it deletes all rows with " National Hunt " in them but it leaves a gap between the last actual row with data in it and the total row A701, could you amend it do bring the row
A701 up with no gap, many thanks.