How to delete a variable number of cells in a column.

[JeffGrant]

Hi all, I have no idea where to start this one.
My data supply company has changed the format of their data.

In Column A is the data as it comes from the data supplier, after a bit of maniluplation, but this is what is left as a starting poit.
In Column C is how the data needs to be and has been presented here for reference.
In Col A, there should only be one blank cell below the cell that contains the letters EW (marked in red). All of the cells between the EW and the start of next record (marked in blue) need to be deleted.
The issue is that that beteen the EW and the next record could be 1 cell or 6 that needs to be deleted.
The number of rows in Col A could easily be into the hundreds of rows, so deleteing these cells manually would be one mega task.
For the purpose of clarity, and as an example, in Col A, the record block starts at 1.Brave Enough (11) and ends at EW. In this case, the record block is 8 cells deep. In other situations, the record block could 6,7 or 9 cells deep. So using code that hard codes the number of cells deep to deleted wont work.
I would really like some vba code that can identify the number of cells that need to be deleted and then delete them with moving the cells up.

My initial thinking is to:

1. Find each incidence of W:
2. Find each incidence of EW
3. Copy the range between W:, with offset(-1,0) to EW to another location
4. Run this through a loop.

But I see a few issues with doing it this way.

Please point me in the right direction. Thanks in Advance.

Column A	Column B	Column C
This is what I get		This is what I need
1. Brave Enough (11)		1. Brave Enough (11)
W: 60.0kgF: 1x7784		W: 60.0kgF: 1x7784
J: Hannah Richardson		J: Hannah Richardson
T: Glenn Richardson		T: Glenn Richardson
27.0027.0031.00		27.0027.0031.00
27​		27​
4.6​		4.6​
EW		EW
Striking Distance
Good distance record		2. Anarchy (5)
1200m | 7:		W: 58.5kgF: 9x2214
3​		J: Jake Molloy
1​		T: L J Hatch
0​		4.204.805.00
		5.5​
2. Anarchy (5)		2.05​
W: 58.5kgF: 9x2214		EW
J: Jake Molloy
T: L J Hatch		3. Legend Rules (8)
4.204.805.00		W: 58.5kgF: 19317x
5.5​		J: Landan Sykes
2.05​		T: Louise White
EW		10.007.006.50
Striking Distance		7​
In Form		2.35​
		EW
3. Legend Rules (8)
W: 58.5kgF: 19317x		4. Poetic Choice (2)
J: Landan Sykes		W: 58.5kgF: 778x57
T: Louise White		J: Sean Cormack
10.007.006.50		T: Jan Pritchard
7​		13.007.006.50
2.35​		7​
EW		2.35​

 

[Micron]

That there could be 1 or x cells in between records is not really the issue. The issue I see is identifying what constitutes the next record. Is it because it is blue? Because it starts with a number followed by a period? Something else? Whatever it is, it has to be consistent and reliable. Something has to define 3. Legend Rules as being the next record, and not "Striking Distance".

EDIT

Please point me in the right direction

That means you want to code it yourself after guidance?

 

[offthelip]

Try this :

Sub test()
Dim outarr()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow + 1, 1))
indi = 1
copr = False
ReDim outarr(1 To lastrow, 1 To 1)
For i = 1 To lastrow
 If Left(inarr(i + 1, 1), 1) = "W" Then
   copr = True
   If indi = 1 Then
    indi = i
   End If
 End If
 If copr Then
  outarr(indi, 1) = inarr(i, 1)
  indi = indi + 1
 End If
 If inarr(i, 1) = "EW" Then
  copr = False
  indi = indi + 1
 End If
Next i
Range(Cells(1, 3), Cells(indi - 1, 3)) = outarr
 
End Sub

 

[JeffGrant]

Thanks Micron & offthelip.

Thanks Offthelip. That is perfect.

I ran this code over several different scenarios with a lot more data and they all came out as required.

I really need to learn more about arrays because many of my loops still run on the sheet ranges not arrays and arrays are clearly faster in their execution.

Thanks again