Creating a table, but not including the last two rows

[Oberon70]

Hi,

I have below code, which is working great. It goes to the Data Sheet and creates a table.

Closedws the variable for the worksheet.

Sub CreateTable()
Closedws.ListObjects.Add(xlSrcRange, Closedws.Range("A$48:" & FindLast(xlFindlastCell)), , xlYes).Name = "Transactions_Table"

End Sub

Findlast is the below function that has been provided to me and it works great. What I want to know if there is way to offset the results from Findlast by -2? Basically, if Findlast says the last sell is BB86 then I would like to offset it to BB84?

Function FindLast(ByVal FindWhat As XLFindLast, Optional ByVal TargetRange As Range) As Variant
    Dim sh               As Worksheet
    Dim RowCol(1 To 2)   As Long, i As Long

    '------------------------------------------------------------------------------------------------------------
    '                                       FindLast Function
    '                                 (update to Ron de Bruin Function)
    '------------------------------------------------------------------------------------------------------------
    'Author     | dmt32
    '------------------------------------------------------------------------------------------------------------
    'Version    | V1 June 2021
    '------------------------------------------------------------------------------------------------------------
    'Purpose    | returns from range with data, last row or last column number or, last used cell address.
    '------------------------------------------------------------------------------------------------------------
    'Parameters |  Name            | Required/Optional | Data type     |   Description
    '           |------------------------------------------------------------------------------------------------
    '           |  FindWhat         |   Required        |   Integer     |   An integer value ( 1 - 3 )
    '           |  TargetRange      |   Optional        |   Range       |   worksheet range
    '------------------------------------------------------------------------------------------------------------
    'Returns    |  Variant
    '------------------------------------------------------------------------------------------------------------
    
    If TargetRange Is Nothing Then Set TargetRange = ActiveSheet.Cells
    Set sh = TargetRange.Parent
    
    FindWhat = IIf(FindWhat > xlFindlastCell, xlFindlastCell, IIf(FindWhat < xlFindLastRow, xlFindLastRow, FindWhat))
    
    On Error Resume Next
    For i = xlRows To xlColumns
        With TargetRange.Find(what:="*", After:=TargetRange.Cells(1), LookIn:=xlFormulas, LookAt:=xlPart, _
                            SearchOrder:=i, SearchDirection:=xlPrevious, MatchCase:=False)
            RowCol(i) = Choose(i, .Row, .Column)
            
        End With
        If RowCol(i) = 0 Then RowCol(i) = 1
    Next i
     On Error GoTo 0
     
    FindLast = IIf(FindWhat = xlFindLastRow, RowCol(xlRows), _
               IIf(FindWhat = xlFindLastColumn, RowCol(xlColumns), _
              sh.Cells(RowCol(xlRows), RowCol(xlColumns)).Address(0, 0)))
End Function

 

[Fluff]

How about

Closedws.ListObjects.Add(xlSrcRange, Closedws.Range("A$48:" & FindLast(xlFindlastCell)-2), , xlYes).Name = "Transactions_Table"

 

[Oberon70]

When I try to use the code above I receive a type mismatch error.

 

[Fluff]

Just noticed, you are missing the A$ from the end of the range.