CODEWITHSUNDEEP
excelvba
OrigamiEye
OrigamiEye

Reputation: 1055

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

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.

Upvotes: 1

Views: 213

Answers (2)

Ike
Ike

Reputation: 13064

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.

IMPORTANT: Always use explicit referencing for a range by referring to worksheet itself. Otherwise the active sheet will be referenced which does not need to be the one you are passing to the function. In this case your current code will throw an error.

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

Upvotes: 0

Paulo A Coelho
Paulo A Coelho

Reputation: 163

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

Upvotes: 2

Related Questions