Help using For Next loop to delete ranges

[hungledink]

I am using excel 2003. I have a spreadsheet that has several individual ranges of data, that are separated by a blank a row. I am trying to create a macro that will look through every cell in column A. If a particular criteria is met, then it will delete the range around that cell, if the criteria isn't met then do nothing. This should leave the remaining ranges, that haven't met the criteria, separated by a blank row.

I currently have this code;

Sub RemovePasses()

Dim LastRow As Integer

LastRow = Sheets("Sheet1").Range("a65536").End(xlUp).Row
For Each c In Sheets("Sheet1").Range("a1:a" & LastRow) '("a1:a19")
If c.Value = "pass" Then c.Offset(-1, 0).Resize(8, 5).Delete
Next

End Sub

This removes the ranges but leaves anywhere between 1 and 3 blank rows between each range. If I change the Resize portion of the code to include the blank row beneath the ranges, then it doesn't remove all of the ranges where the criteria is met.

Hope that makes sense.:bug:

I have attached a sample workbook with original data in Sheet1 and then a sheet with the intended outcome.

Thanks in advance.

[patel]

is the string pass always on the second row of each range ? if yes
Sub RemovePasses()

Dim LastRow As Integer
LastRow = Sheets("Sheet1").Range("a65536").End(xlUp).Row
For r = LastRow To 1 Step -1
Set c = Cells(r, 1)
If c.Value = "pass" Then c.Offset(-1, 0).Resize(9, 5).Delete
Next

End Sub

[hungledink]

The range is always a set number of rows but the value 'pass' is not always in the same row. This workbook is just test data I've been working on.

Another problem I have is that although its always a certain number of rows, there maybe blanks in the range.

[patel]

If there are blanks in the range how can I identify each range ?

[hungledink]

Thanks for your help so far.

I will be able to add data to define the start and finish of each each range. So for example, I can add the value 'start' in column A in the first row of the range, and the value 'end' in column a in the last row of each range. The reason for the strange layout is the data is an extracted report form other software and there is no way to customise the formatting.

So my data may consist of let's say 10 ranges. Each range will have in column A, at the start of the range, the value 'start', and at the end will have the value 'end' and in on of the rows between these 2 will be the 'pass' criteria.

If its easier to delete the entire rows instead of using the resize function that's would be ok.

[patel]

I need a good sample file with some possible cases of ranges

[HaHoBe]

Hi, hungledink,

maybe give this code a try (caveat: will need an empty row at the start of the data or in other words two rows above the first hit):

Sub hungledink()
Dim rngFound As Range
Do While rngFound Is Nothing
Set rngFound = Cells.Find(what:="pass", lookat:=xlWhole)
If Not rngFound Is Nothing Then
Set rngFound = Union(rngFound.CurrentRegion, Cells(rngFound.CurrentRegion.Cells(1).Row - 1, 1))
rngFound.EntireRow.Delete
Set rngFound = Nothing
End If
Loop
End Sub Ciao,
Holger