Fill down the cell based on the cell value

[mayaa_mmm]

I used to download the report from our site, it always gives the report in the below format.

[TABLE="width: 343"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B [/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Class [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Subject [/TD]
[TD]Maths[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Mark details[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Semester I[/TD]
[TD]Semester II[/TD]
[TD]Semester III[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]30[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]25[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]20[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]Class [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Subject [/TD]
[TD]Science[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Mark details[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Semester I[/TD]
[TD]Semester II[/TD]
[TD]Semester III[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]30[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]20[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]25[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]


I want the report in the below format.
Every time i need to remove the rows and manipulate the output to get the below output format.

[TABLE="width: 423"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Subject[/TD]
[TD]Semester I[/TD]
[TD]Semester II[/TD]
[TD]Semester III[/TD]
[/TR]
[TR]
[TD]Maths[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]Maths[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]Maths[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Science[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]Science[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Science[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]100[/TD]
[/TR]
</tbody>[/TABLE]


Kindly help with this issue

 

[Peter_SSs]

Try this with a copy of your workbook.
It may need some tweaking as website data is often not as 'clean' as other data.

Sub Rearrange()
  Dim Data, Results
  Dim ubResults As Long, i As Long, k As Long
  Dim Subj As String
  
  Data = Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(1)).Resize(, 3).Value
  ubResults = Application.Count(Columns("A"))
  ReDim Results(1 To ubResults, 1 To 4)
  k = 1
  Do
    i = i + 1
    If Data(i, 1) = "Subject" Then
      Subj = Data(i, 2)
      i = i + 3
      Do While IsNumeric(Data(i, 1)) And i < UBound(Data, 1)
        Results(k, 1) = Subj
        Results(k, 2) = Data(i, 1)
        Results(k, 3) = Data(i, 2)
        Results(k, 4) = Data(i, 3)
        k = k + 1
        i = i + 1
      Loop
    End If
  Loop Until i >= UBound(Data, 1)
  Range("G2").Resize(ubResults, 4).Value = Results
  Range("G1:J1") = Array("Subject", "Semester I", "Semester II", "Semester III")
End Sub