hungledink
05-04-2013, 05:21 AM
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.
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.