Processing four data tables in a worksheet. Excel adds digits to the end of the name. How do I stop that?

[OaklandJim]

I am looping through tables in a worksheet named Sheet1. But for some reason Excel adds digits to the end of names in the loop.

Tables are used to record expenses. Table names are Transportation, Accommodations, Activities and Meals.

Code below generates the following Debug.Print output

Table name = Transportation812
Table name = Accommodations913
Table name = Activities1014
Table name = Meals1115

Sub DeleteEmptyTableRows()

    Dim wsExpenses As Worksheet

    Dim tDataTable As ListObject

    Dim iTableRow As Long

    Dim iDateColumn As Long

    Dim avDataArray() As Variant

    Set wsExpenses = Worksheets("Sheet1") '<= change this if the Expenses worksheet name changes

    iDateColumn = 2 '<= change this if the date column number changes

    For Each tDataTable In wsExpenses.ListObjects

        With tDataTable

Debug.Print "Table name = " & .Name

'           Only process specific tables in the Expenses worksheet.
            If .Name = "Transportation" Or _
               .Name = "Accommodations" Or _
               .Name = "Activities" Or _
               .Name = "Meals" _
             Then

'               Put table contents into an array.
                avDataArray = .DataBodyRange

'               Loop through each item in Date Column of Table
                For iTableRow = LBound(avDataArray) To UBound(avDataArray)

                    If avDataArray(iTableRow, iDateColumn) = "" _
                     Then
                        '.ListRows(iTableRow).Delete
                    End If

                Next iTableRow

            End If

        End With

    Next tDataTable

End Sub

 

[Skyybot]

What comes up in the Name Manager?

 

[OaklandJim]

Good question. Oddly, indeed the table names include those digits. BUT, I certainly did not name them that way! Thanks.

 

[OaklandJim]

Unfortunately, Excel keeps adding those digits to the end of table names. I wish that I could stop that. It is a big hassle.

 

[OaklandJim]

Unsatisfyingly, I guess I'll have to resort to the following function to trim those digits off for my sub. BUT that leaves tables with the wrong name. A hassle when doing stuff with the tables like getting totals and referring to them in formulas.

Option Explicit

Function TrimDigitsFromString(psString As String) As String

    Dim iLen As Long
    
    Dim iCharNum As Long
    
    iLen = Len(psString)

    TrimDigitsFromString = "?"
    
    For iCharNum = 1 To iLen

        If Right(psString, 1) Like "#" _
         Then
            
            psString = Left(psString, Len(psString) - 1)

        Else
            TrimDigitsFromString = psString
            
            Exit Function

        End If
        
    Next iCharNum
    
End Function

 

[OaklandJim]

I found that if I make a copy of a worksheet with tables that the digits are added to the table name. I guess that makes sense if the same table name is already present in another worksheet. That is an issue because I need the same tables in more than one worksheet.

 

[Peter_SSs]

I have removed the 'Mark as solution' from post #6 since it doesn't actually answer the question asked. This will save future readers from coming to find an answer to the question then finding there isn't an answer.

 

[OaklandJim]

Hello Peter. I learned the hard way that there cannot be more than one table in a workbook with a given name. Specifically, I thought that table names were specific to a worksheet not to a workbook. I imagined several similar worksheets -- such as regional sales amounts -- each containing a table with the same name as tables in other worksheets. When I copied a worksheet the tables had the same name plus digits after the name. Once I realized the issue I marked my own post as a solution. It seems that someone investigating the issue that I was having -- digits added to the name of tables -- might benefit from my realization.