Use an item from one array inside a loop action for a different array Excel VBA-CodePudding

I have created two Array's. One holds the names of tables I want to modify and the other holds the name of the sheet that the table is on. The sheets are hidden.

I want to loop through the list of tables, make the corresponding sheet visible, modify the size of the table, and then make it hidden again. There are two variations of the resizing, one is to make the table match the size of data entry table and the other is to return them to just header and one row.

This is the code for the second option.

Sub ReturnTableToOneRow()

    Dim tblNames(1 To 16) As String
    Dim shNames(1 To 16) As String
    Dim item As Variant
    Dim shNum As Integer
    Dim ws As Worksheet
    Dim TblNa As String
    Dim ShNa As String
    Dim uB As Integer, lB As Integer
         
        tblNames(1) = "Calculator"
            '... to truncate the data but it exists in my code
        tblNames(16) = "PC"
        
        shNames(1) = "Calculator"
            '... to truncate the data but it exists in my code
        shNames(16) = "Reimbursements"

    shNum = 1
    
    For Each item In tblNames

The For is looping through a list of table names and this grabs the corresponding sheetname from the other list

    ShNa = shNames(shNum) 
    
    ws = ShNa
    
    ws.Visible = xlSheetVisible

The following code requires that the sheet be visible for it to work. The purpose of this code is to delete all data from the table and reset it to just a header row and one data row containing all the formulas.

Application.Range(item).ListObject.DataBodyRange.Offset(1, 0).Resize(Application.Range(item).ListObject.DataBodyRange.Rows.Count - 1, _
Application.Range(item).ListObject.DataBodyRange.Columns.Count).Rows.Delete

Lastly the iteration increments the ShNum for the next iteration to get the correct sheet name and then sets the sheet to hidden before looping to the next table in the list.

    shNum = shNum   1

    ws.Visible = xlSheetHidden

Next item

The code works and does its job if I write it specifically for each table like this:

Application.Range("Calculator").ListObject.DataBodyRange.Offset(1, 0).Resize(Application.Range("Calculator").ListObject.DataBodyRange.Rows.Count - 1, _
Application.Range("Calculator").ListObject.DataBodyRange.Columns.Count).Rows.Delete

But I can't figure out how to get it to work with the current item which is the table name. The error is runtime 1004 application-defined or object-defined error

CodePudding user response：

Maybe something like this:

Option Explicit

Sub ReturnTableToOneRow()

    Dim arr() As String, i As Long, ws As Worksheet
    
    arr = TableInfo()
    For i = LBound(arr, 1) To UBound(arr, 1)
        Set ws = ThisWorkbook.Worksheets(arr(i, 2))
        ws.Visible = xlSheetVisible
        With ws.ListObjects(arr(i, 1)).DataBodyRange
            'check there are rows to delete
            If .Rows.Count>1 Then
                .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
            End If
        End With
        ws.Visible = xlSheetHidden
    Next i
End Sub

'return the list of tables/sheets
Function TableInfo() As String()
    Dim arr(1 To 16, 1 To 2) As String
    'maybe neater to use a single 2-d array here
    arr(1, 1) = "Calculator"  'table name
    arr(1, 2) = "Calculator"  'sheet name
    '... etc
    '... etc
    arr(16, 1) = "PC"
    arr(16, 2) = "Reimbursements"
    TableInfo = arr
End Function