+ Reply to Thread
Results 1 to 5 of 5

List out Column Headers with empty data

Hybrid View

Pavan Renjal List out Column Headers with... 09-30-2015, 03:12 PM
AlphaFrog Re: List out Column Headers... 09-30-2015, 03:30 PM
Pavan Renjal Re: List out Column Headers... 10-01-2015, 01:20 AM
AlphaFrog Re: List out Column Headers... 10-01-2015, 01:23 AM
Pavan Renjal Re: List out Column Headers... 10-01-2015, 01:38 AM
  1. #1
    Registered User
    Join Date
    12-24-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    99

    List out Column Headers with empty data

    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

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: List out Column Headers with empty data

    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
    Last edited by AlphaFrog; 09-30-2015 at 03:35 PM.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    12-24-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    99

    Re: List out Column Headers with empty data

    Thanks Alphafrog! This is exactly what I needed! Also, how to display the headers in a single column? Right now the header values are appearing across the columns in a single row. I tried experimenting with the Resize property, just couldn't find a way to display them in a single column. Please help me understand the below code - how this works:

    Sheet2.Cells(1, 1).Resize(1, j).Value = Header

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: List out Column Headers with empty data

    You're welcome.

    Sheet2.Cells(1, 1).Resize(j, 1).Value = Application.Transpose(Header)

  5. #5
    Registered User
    Join Date
    12-24-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    99

    Re: List out Column Headers with empty data

    Thanks AlphaFrog!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Send Data from Dropdown List to Multiple Sheets in first empty cell in a specified column
    By majime01 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-15-2015, 06:17 AM
  2. List headers where column contains X
    By csepizoli in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-14-2015, 08:42 AM
  3. how to list non-empty values from a column of data.
    By setilvenstre in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-04-2012, 03:01 AM
  4. List Form that populates with column headers
    By theduckman16 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-04-2011, 07:41 PM
  5. creating list of column headers
    By rfhurley in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-27-2006, 06:45 PM
  6. creating a list of column headers
    By rfhurley in forum Excel General
    Replies: 1
    Last Post: 07-27-2006, 06:42 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1