deleting range of rows if ..

[travist]

Hi all,

I have a large spreadsheet that needs to be sorted. How can i check every 14th cell in the same column and delete that row and the 9 above and below it if it does not contain a certain string? Any help would be greatly appreciated.

Thanks

[Nigel_hough]

Hi

Had a little read through of your problem i think this is pretty much the sort of code your looking for. It probably needs to be tweaked to your specific format but hope it helps.


Sub sort()
'select start point
Range("A1").Select
'offset selection to the first 14th cell
Selection.Offset(13, 0).Select

'Loop this till the end
Do While (Selection <> "")
'if the cell containd the specific string
If (Selection Like "*String*") Then
'select the nine above and the nine below
Range("A" & Selection.Offset(-9, 0).Row & ":A" & Selection.Offset(9, 0).Row).Select
'Then delete them
Selection.Delete
'Not sure about this bit it selects 4 cells down which is the 14th after the delete
Selection.Offset(4, 0).Select

Else
'if it dosen't contain the string go to the next 14 rows down
Selection.Offset(14, 0).Select

End If

Loop

End Sub