I have this table below wherein I would like to get the data below the blank cell in between "Participant" and "Close"
Note: <> means Blank Cell
I have this code below but It will also get the data beyond the "Participant" and "Close"
Note: <> means Blank Cell
<> |
---|
103 |
105 |
Open |
<> |
Participant |
<> |
Heather |
103 |
105 |
<> |
Lawrence |
102 |
<> |
Judel |
105 |
1042 |
<> |
Close |
<> |
Open |
I have this code below but It will also get the data beyond the "Participant" and "Close"
VBA Code:
Sub Golf()
Application.ScreenUpdating = False
Dim Sh As Worksheet, Sr As Range, Sf As Range, wA As Variant
Dim Sf1 As Range, wA1 As Variant
Const FindWhat As String = ""
Set Sh = Sheets("Golf Paste Here")
Set Sr = Sh.Range("A1:A80000")
sRow = 2
Set Sf = Sr.Find(FindWhat, , xlValues, xlPart)
If Not Sf Is Nothing Then
cell = Sf.Address
Do
wA = Sf.Resize(2, 1).Value
With Sheets("Sheet2")
.Range("A" & Rows.Count).End(xlUp)(2).Resize(2, 1).Value = wA
.Range("C" & sRow).Resize(1, 2).Value = Application.Transpose(wA)
sRow = sRow + 1
End With
Set Sf = Sr.FindNext(Sf)
Loop While Not Sf Is Nothing And Sf.Address <> cell
End If
Application.ScreenUpdating = True
End Sub