Public Sub ECG()
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim lastRow As Long
Dim lastCol As Long
Dim thisRow As Long
Dim thisCol As Long
Dim nextRow As Long
' Set the source sheet and find the last row and column
Set sourceSheet = ActiveSheet
With sourceSheet
lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
' Create a new target sheet and add the headers
Set targetSheet = Worksheets.Add
With targetSheet
.Cells(1, 1).Value = "Patient"
.Cells(1, 2).Value = "Test"
.Cells(1, 3).Value = "Result"
End With
nextRow = 2
' Process the grid
For thisRow = 2 To lastRow
For thisCol = 2 To lastCol
If sourceSheet.Cells(thisRow, thisCol).Value <> "" Then
targetSheet.Cells(nextRow, 1).Value = sourceSheet.Cells(thisRow, 1).Value
targetSheet.Cells(nextRow, 2).Value = sourceSheet.Cells(1, thisCol).Value
targetSheet.Cells(nextRow, 3).Value = sourceSheet.Cells(thisRow, thisCol).Value
nextRow = nextRow + 1
End If
Next thisCol
Next thisRow
End Sub