<<Convert Excel Spreadsheets to Web Pages \| Trading Software That Operates Within Excel \| Convert Excel, Access & Other Databases \| Merge Excel Files>>

Excluding Headings/Headers From the Current Region/Table

| | Information Helpful? Why Not Donate.

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle

Redefine a Range so That no Headings are Included

Got any Excel Questions? Free Excel Help .

Lot's More: Excel VBA . See Also:Excel Ranges

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").CurrentRegionEnd 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 RangeDim 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 dataAND 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

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALLpurchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Try out:Analyzer XL |Downloader XL |Smart VBA |Trader XL Pro (best value) |ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader PackageTechnical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft