XLA routines: EE_Table

Nick's picture
If you use Excel as a database, EE_Table will return the range object of your data. You can then manipulate this. uses EE_GetLastPopulatedCell to find the last cell of the table
Function EE_Table(strHeaderString As String, wks As Worksheet) As range
'> - takes a heading string, and a worksheet name
'> - returns a range object that represents the table around the data
'> - code will look for the heading on the target sheet, then assume this is
'> row1.
'> - it will then work out the start row, end row, start col and end col and
'> return the range.
    Dim rng     As range
    Dim rngTemp As range
    On Error Resume Next
        Set rng = wks.Cells.Find(what:=strHeaderString, LookIn:=xlValues, lookat:=xlWhole).CurrentRegion
    On Error GoTo 0
    If Not rng Is Nothing Then
        Set rngTemp = rng.CurrentRegion
        Set EE_Table = range(wks.Cells(rng.Row, rngTemp.Column), EE_GetLastPopulatedCell(wks))
    End If
    Set rngTemp = Nothing
    Set rng = Nothing
End Function