Macro / If Function ?

[crooser100]

I have a problem where by I have a whole workbook containing numerical data that I need to analyse.

I am faced with an issue where I need to ignore some rows after finding data that occurs within a column.

For example....

I have say 20 rows accross and 30,000 down. In one of the columns it has either the number 1 or 2. I want to find where the data changes from a 1 to a 2, then either delete or ignore the next 20 rows worth of data, but for the whole column.

This will be an ongoing task and would be really useful to have a macro or formula to do it for me...

Any suggestions much appreciated.

 

[JamesW]

Hi there,

Try these (assuming the column is column C and the data starts at row 2)

Delete:

Sub delete Rows()
        lRow = Range("C" & Rows.Count).End(xlUp).Row
    For i = lRow To 2 Step -1
        If Cells(i, 3).Value = 2 And Cells(i - 1, 3).Value = 1 Then Cells(i, 3).EntireRow.Delete
    Next
End Sub

Ignore 20 rows (this wont do anything with the data though):

Sub skipRows()
    lRow = Range("C" & Rows.Count).End(xlUp).Row
    For i = 2 To lRow
        If Cells(i, 3).Value = 2 And Cells(i - 1, 3).Value = 1 Then i = i + 20
    Next
End Sub