I am trying to write a macro to retrieve the list of all column headers which don't contain any data. I took some references and tried to modify but somehow I can't get it right. I want the column headers listed in a different sheet.

Here's my code:


Public Sub getColumnHeaders()
Dim header() As String
Application.ScreenUpdating = False
ActiveSheet.UsedRange.Select
LastCol = Sheet1.cells(1, Sheet1.Columns.Count).End(xlToLeft).Column
Dim j As Integer
j = 0
For I = LastCol To 1 Step -1
Lastrow = ActiveSheet.cells(Rows.Count, I).End(xlUp).Row
If Lastrow = 1 And cells(Lastrow, I) = "" Then
header(j) = Sheet1.cells(1, I).Value
j = j + 1
End If
Next I

For k = 1 To j
Sheet2.cells(1, I) = header(k)
Next k

Application.ScreenUpdating = True
End Sub