Programmatically finding the header row on a worksheet 2

[VBAjedi]

I'm sketching out a cool generic tool (probably destined to be an Add-In) that will create a report using data from other sheets in the current workbook. More detail on that in a minute, but first right to my question:

For a given worksheet, how can I reliably identify the block of cells containing the headers? Assumptions: the worksheet contains one block of data with one header row, the header row is probably not on row one, and there may be miscellaneous data above the header row (worksheet titles, Subtotal formulas, etc). Because this is for a generic tool that can be used in any workbook, assume I'll know nothing else about the sheet.

In case you care why I'm asking: my code will be creating a sheet with dropdown boxes in each column of row one that will be dynamically populated with the names of all other sheets in the current workbook. That's easy enough, but once the user selects a sheet name in a given column, I want to populate another dropdown box (in row 2 of that same column) with the column headers from the specified sheet (thus my question).

Once the user selects the desired column, my code will go out and copy the data in that column to the report sheet. The idea is to have an on-the-fly report builder where columns can be swapped in and out like building blocks. Similar in flexibility to a Pivot Table, but the final result is a standard worksheet.

As always, thanks in advance for your invaluable input!

VBAjedi

 

[SkipVought]

Hi,

Will there be a user verify feature, in case the assumptions you make select the wrong thing?

There is an Areas Collection...

There’s no singular Area object; individual members of the Areas collection are Range objects. The Areas collection contains one Range object for each discrete, contiguous range of cells within the selection. If the selection contains only one area, the Areas collection contains a single Range object that corresponds to that selection.

Frankly, I have never been able to use the Areas collection.

If your contiguous areas are vertically oriented, ie you don't have to search to the right of the first contiguous area for other contiguous areas; just DOWM, then you can use the Find method to find the first cell containing a value, use the CurrentRegion to return the contiguous range, use the End(xlDown) method (twice) to find the first cell of the next contiguous area, use the CurrentRegion to return the contiguous area, etc.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[HughLerwill]

A sheet's 'Title' rows are unlikely to need more than 1 or 2 columns (an option perhaps) so you could just go for the first row containing anything in the 2nd/ 3rd column.

 

[AnotherHiggins]

This line: "Assumptions: the worksheet contains one block of data..." should allow you to find the header row pretty easily.

Give this a shot:

Sub test()
    Dim dblAvailRows                            As Double
    Dim dblAvailCols                            As Double
    Dim intLstRow                               As Integer
    Dim intLstCol                               As Integer
    Dim rngHeaderRow                            As Integer

    '   Total Rows And Columns available in this version of Excel
    '**********************************************************
    dblAvailRows = ActiveSheet.Rows.Count
    dblAvailCols = ActiveSheet.Columns.Count

    intLstRow = Cells.Find( _
            What:="*", _
            After:=Cells(dblAvailRows, dblAvailCols), _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious).Row
    intLstCol = Cells.Find( _
            What:="*", _
            After:=Cells(dblAvailRows, dblAvailCols), _
            SearchOrder:=xlByColumns, _
            SearchDirection:=xlPrevious).Column

    rngHeaderRow = _
            Cells(intLstRow, intLstCol).CurrentRegion.Cells(1, 1).Row

    'Then you can refer to the header row like this:
    '***********************************************
    Range(Cells(rngHeaderRow, 1), Cells(rngHeaderRow, dblLstCol)).Select

End Sub

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[VBAjedi]

Skip,
CurrentRegion... that might do it! Since any data sheet I design or "sanction" has a single data range with nothing below it, I should be able to find the last used cell on the sheet, then use the first row of CurrentRegion as my header row. Sound reasonably resilient?


Hugh,
Thanks for the suggestion! I'm fond of using the Autofilter on my worksheets, coupled with one or more rows above the header row containing SUBTOTAL() formulas to generate dynamic totals based on the visible rows. So I often have stuff that stretches the width of the data in the cells above the header row...

VBAjedi

 

[xlbo]

I'm liking John's code for this

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[VBAjedi]

John,

You must have posted while I was replying... that's about what I was picturing. Nice! Here's what I ended up with:

Sub GetHeaderCellsRange(TargetSheet As Worksheet, ByRef HeaderCellsRange As Range)
  Dim LastCell As Range
  Set LastCell = TargetSheet.Cells.Find(what:="*", after:=Range("IV65536"), _
                  searchorder:=xlByRows, searchdirection:=xlPrevious)
  Set HeaderCellsRange = TargetSheet.Range(LastCell.CurrentRegion.Cells(1, 1), LastCell.CurrentRegion.Cells(1, LastCell.CurrentRegion.Columns.Count))
End Sub

Sub TestIt()
  Dim HeaderCells As Range, c As Range
  GetHeaderCellsRange TargetSheet:=ActiveWorkbook.Sheets("Sheet1"), HeaderCellsRange:=HeaderCells
  For Each c In HeaderCells
    Debug.Print c.Value
  Next c
End Sub

I decided to have the Sub return a Range object instead of an array filled with the column names because the Range object will be more immediately useful when I start copying columns, etc.

Anyway, stars for you and Skip... thanks again!

VBAjedi

 

[xlbo]

I think that

Set LastCell = TargetSheet.Cells.Find(what:="*", after:=Range("IV65536"), _

is a little dangerous...I particularly liked John's use of

dblAvailRows = ActiveSheet.Rows.Count
dblAvailCols = ActiveSheet.Columns.Count
...
Cells.Find( _
What:="*", _
After:=Cells(dblAvailRows, dblAvailCols),

To provide portability over multiple versions of excel...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[VBAjedi]

LOL... you can tell what version of Excel I learned VBA in! I'm used to working in older versions still but I suppose it would be good of me to consider the possibility of newer versions. Thanks for that reminder!

I can skip defining and assigning those two variables though, right? As in:

Sub GetHeaderCellsRange(TargetSheet As Worksheet, ByRef HeaderCellsRange As Range)
  Dim LastCell As Range
  Set LastCell = TargetSheet.Cells.Find(what:="*", after:=Cells(TargetSheet.Rows.Count, TargetSheet.Columns.Count), _
                  searchorder:=xlByRows, searchdirection:=xlPrevious)
  Set HeaderCellsRange = TargetSheet.Range(LastCell.CurrentRegion.Cells(1, 1), LastCell.CurrentRegion.Cells(1, LastCell.CurrentRegion.Columns.Count))
End Sub

Thanks again!

VBAjedi

 

[xlbo]

Yup - you can do that. It might be useful to set them up as global variables though - if you need to reference them again it would be good practice not to perform the same operation mutliple times - depends whether you think you may need to know the max number of rows / columns again during the execution of this code...

the 65536 is something that I used to use extensively but have started working in places where 2007 / 2010 is the norm so am having to change my coding habits!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[VBAjedi]

Geoff,

If memory serves me correctly I think I got that lastrow method from you or PHP here at Tek-Tips six or seven years ago... I remember being so excited that I could that with a single line of code.

I definitely blame the crew in this forum from that era for my obsession with finding the shortest, most efficient, and easiest to read code to get something done (and yes, I want short, efficient, AND easy to read in a single approach!)

;-)


VBAjedi

 

[VBAjedi]

Getting pretty far off-topic now, but here's a trip down memory lane. SkipVought, PHV, and xlbo all working to help me find the shortest code to do something:

thread707-885934

Interesting that my obsession with creating "generic"/user-configurable tools was in full swing back in 2004.

VBAjedi

 

[SkipVought]

Many moons ago!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

yeah - not sure I provided any help there ;-)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question