Excluding Headings/Headers From the Current Region/Table

< Back to Search results

 Category: [Excel]  Demo Available 

Excluding Headings/Headers From the Current Region/Table


Redefine a Range so That no Headings are Included

Got any Excel/VBA Questions? Free Excel Help.


Excel: Excluding Headings/Headers from the Current Region/Table

It's often that one needs to work on a range of data, or table, but NOT include any headings/headers. This can be done very easily with the ListHeaderRows Property of an Range Object. Before we can use the ListHeaderRows Property we must first determine the table range. If your table is set up correctly (no blank rows or columns) this can be done very easily via the CurrentRegion Property. That is,

Sub DetermineGoodTable()

Dim rTable As Range

    rTable = Sheet1.Range("A1").CurrentRegion

End Sub

However, if your table DOES include blank rows or columns, we must find the outer top left and bottom right cell. This can be done like below where we know A1 to be our top left cell of the table.

Sub DetermineBadTable()

Dim rTable As Range

    With Sheet1

        Set rTable = .Range(.Range("A1"), _

               .Cells(65536, .Range("IV1").End(xlToLeft).Column).End(xlUp))

    End With


End Sub

Ok, now we have determined and set a Range variable (rTable) to out table range it's time to redefine the Range variable so no headers/headings are included. Here is how via the use of the ListHeaderRows Property.

Sub GoodTableWithHeaders()

Dim rTable As Range

Dim lHeadersRows As Long

    Set rTable = Sheet1.Range("A1").CurrentRegion

    lHeadersRows = rTable.ListHeaderRows


    'Resize the range minus lHeadersRows rows

    If lHeadersRows > 0 Then

        Set rTable = rTable.Resize(rTable.Rows.Count - lHeadersRows)

        'Move new range down to Start at the first data row.

        Set rTable = rTable.Offset(1)

    End If


End Sub

What Constitutes a Heading/Header Row

If your table is numeric data and you headings are text (or vice verca), Excel will assume row 1 of the table as a header row. However, if your data AND headings are both numeric, or both text, Excel will consider your table as having NO headers. The way to overcome this is to make your headings different to that of the data. This can be done via bolding, font color/size etc.

Or, should you simply know for a fact that row 1 of the table IS a header row you can use the code below;

Sub GoodTableDataHeaders()

Dim rTable As Range

    Set rTable = Sheet1.Range("A1").CurrentRegion


        Set rTable = rTable.Resize(rTable.Rows.Count - 1)

        'Move new range down to Start at the fisrt data row.

        Set rTable = rTable.Offset(lHeadersRows)


End Sub

See also:

Excel Ranges: Finding the Last Cell in a Range
Excel: Get File Name From User to Open Workbook Or Save Workbook
Excel VBA Runtime Errors & Preventing Them
Sheet/Worksheet CodeNames
Automatically Run Excel Macros via Workbook & Worksheet Events
Excel: Password Protect/Unprotect All Excel Worksheets in One Go


See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


stars (0 Reviews)