How to format data as a table of variable size in VBA Excel?-CodePudding

I have loaded data into Excel and want to format the data in a table with a function where I pass the sheet and the number of rows.

Currently I have

Sub FormatTable(workingSheet, N)
        workingSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$I$90"), , xlYes).Name = "Vari Table"
End Sub

The size of the table is hardcoded to 90. How do I make it N, the number I pass into the function. Escaping the 90 and replacing it with N does not work.

CodePudding user response：

Sub FormatTable(workingSheet, N)
        workingSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$I$" & N), , xlYes).Name = "Vari Table"
End Sub

CodePudding user response：

I would enhance your procedure like this:

Public Function insertTable(ws As Worksheet, numberOfRows As Long, nameOfTable As String) As ListObject
    Dim rg As Range
    Set rg = ws.Range("$A$1:$I$" & numberOfRows)
    
    Dim lo As ListObject
    Set lo = ws.ListObjects.Add(xlSrcRange, rg, , xlYes)
    
    lo.Name = nameOfTable
    
    Set insertTable = lo
End Function

Basic solution to your question: you have to add the number with a string concatination.

This function returns the created ListObject- just in case you need it for further actions.