Try this...
Public Sub getColumnHeaders()
Dim Header As Variant, LastCol As Long, LastRow As Long, i As Long, j As Long
With Sheet1
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
ReDim Header(1 To LastCol)
For i = 1 To LastCol
LastRow = .Cells(Rows.Count, i).End(xlUp).Row
If LastRow = 1 And .Cells(1, i) <> "" Then
j = j + 1
Header(j) = .Cells(1, i).Value
End If
Next i
End With
If j > 0 Then
Sheet2.Cells(1, 1).Resize(1, j).Value = Header
Else
MsgBox "No Headers with empty columns found.", vbExclamation, "No Empty Headers"
End If
End Sub
Bookmarks