It is probably blindingly obvious but I am stumped - logic is explained below
<colgroup><col span="3"><col><col><col span="4"><col><col></colgroup><tbody>
</tbody>
ID | COLOR | TEMP | StartDate | EndDate | Y/N | |||||
123ABC | green1 | hot | 01/01/2017 | Y | ||||||
123ABC | 01/01/2017 | Y | ||||||||
123ABC | red2 | hot | 01/01/2017 | 31/01/2017 | Y | |||||
123ABC | hot | 01/01/2017 | macro | |||||||
123ABC | hot | 01/01/2017 | Y | |||||||
123ABC | green3 | 01/01/2017 | 31/01/2017 | rrow = 39 | ||||||
123ABC | 01/01/2017 | Y | For k = 1 To 2 | |||||||
123ABC | hot | 01/01/2017 | Y | Match = Cells(k + 32, 1) | ||||||
456DEF | green4 | hot | 08/01/2017 | 31/01/2017 | Y | For j = 2 To 26 | ||||
456DEF | 08/01/2017 | Y | If Cells(j, 1) = Match Then GoTo 20 Else GoTo 50 | |||||||
456DEF | green5 | hot | 08/01/2017 | 31/01/2017 | Y | 20 If Cells(j, 2) <> "" And Cells(j, 3) <> "" And Cells(j, 4) <> "" Then GoTo 40 Else GoTo 50 | ||||
456DEF | hot | 08/01/2017 | 40 If Cells(j, 5) <> "" And Cells(j, 6) <> "" Then GoTo 45 | |||||||
456DEF | hot | 08/01/2017 | Y | 45 rrow = rrow + 1 | ||||||
456DEF | green6 | 08/01/2017 | 31/01/2017 | For z = 1 To 7 | ||||||
456DEF | 08/01/2017 | Y | Cells(rrow, z) = Cells(j, z) | |||||||
456DEF | hot | 08/01/2017 | Y | Next z | ||||||
456DEF | green7 | hot | 08/01/2017 | Y | 50 Next j | |||||
456DEF | 08/01/2017 | Y | Next k | |||||||
123ABC | green8 | hot | 08/01/2017 | 31/01/2017 | Y | End Sub | ||||
123ABC | hot | 08/01/2017 | ||||||||
123ABC | hot | 08/01/2017 | Y | |||||||
123ABC | green9 | 08/01/2017 | 31/01/2017 | |||||||
123ABC | 08/01/2017 | Y | the macro is meant to look for all the 123ABC rows (as defined in A33) | |||||||
123ABC | red1 | hot | 08/01/2017 | 31/01/2017 | Y | that have no blanks in columns 2 to 6 | ||||
123ABC | 08/01/2017 | Y | row 26 | and print them from row 40 | ||||||
col F | and then repeat the search for 456DEF (A34) | |||||||||
I cannot figure out why the very first row (color = green1) | ||||||||||
appears in the output table | ||||||||||
123ABC | row 33 | |||||||||
456DEF | I have checked that E2 really is a blank cell by copying another blank cell into it | |||||||||
123ABC | green1 | hot | 01/01/2017 | Y | ||||||
123ABC | red2 | hot | 01/01/2017 | 31/01/2017 | Y | |||||
123ABC | green8 | hot | 08/01/2017 | 31/01/2017 | Y | |||||
123ABC | red1 | hot | 08/01/2017 | 31/01/2017 | Y | |||||
456DEF | green4 | hot | 08/01/2017 | 31/01/2017 | Y | |||||
456DEF | green5 | hot | 08/01/2017 | 31/01/2017 | Y | |||||
456DEF | green7 | hot | 08/01/2017 | Y | ||||||
<colgroup><col span="3"><col><col><col span="4"><col><col></colgroup><tbody>
</tbody>