I am working with datasets that have many thousands of rows. I am looking to select the rows including and between the start and end of a given condition (Nominal cell values) and export only those rows of interest to a new sheet.
The condition may start and end several times with other unimportant rows between the end of the previous condition and the start of the next same condition, so I seek to loop the operation down the current active sheet.
The conditions are located in the 'W' column and my excel sheets generally contain about 63,000 rows each.
I have a working code which I provide below, but I keep getting errors on the basis of 'For without Next'. I am wondering if anyone may have insight as to what is wrong with my code.
Sub exportconditionstarttoend() Dim rownum As Long Dim colnum As Long Dim startrow As Long Dim endrow As Long Dim lastrow As Long rownum = 1 colnum = 1 lastrow = Worksheets("ETM ETM0007").Range("W63000").End(xlUp).Row With ActiveWorkbook.Worksheets("ETM ETM0007").Range("W1:W" & lastrow) For rownum = 1 To lastrow Do If .Cells(rownum, 1).Value = "Condition 1" Then startrow = rownum End If rownum = rownum + 1 If (rownum > lastrow) Then Exit For Loop Until .Cells(rownum, 1).Value = "Condition 1 - End" endrow = rownum rownum = rownum + 1 Worksheets("StartSheet").Range(startrow & ":" & endrow).Copy Sheets("Result").Select Range("W1").Select ActiveSheet.Paste End Sub
Thank you for your help!