Table refresh vba excel Call procedure from another procedure Error Code 1004

Question

I have a call procedure to clear contents of tables across multiple worksheets. This procedure is invoked only from the 2nd sheet of the workbook. When I invoke this, I am getting Error 1004 "Application-defined or Object-defined error".

Below is the parent code base invoking the sub procedure:

Sub ValidateData_BDV1()
On Error Resume Next
Err.Clear

'''''Define Variables'''''''''
Dim mySheet As Worksheet
Dim mySheetName As String
Dim bdvName As Variant
Dim sqlQuery As String
Dim connectStr As String
Dim wsMatch As Worksheet

Dim myWorkbook As Workbook: Set myWorkbook = ThisWorkbook

'''''''''Set Variables''''''''
cancelEvent = False
Set mySheet = ActiveSheet                   'Sets mySheet variable as current active sheet
mySheetName = mySheet.Name

driverName = mySheet.Range("B1").Value2     'Get the value of the TDV driver
' MsgBox driver
dataSourceName = mySheet.Range("B3").Value2 'Get the data source name for the published TDV database
' MsgBox dataSourceName
schemaName = mySheet.Range("B5").Value2     'Get the schema name of the published tdv view
bdvName = mySheet.Range("B6").Value2        'Get the name of the published BDV

''''''''''Refresh data across sheets'''''''''''''
Application.ScreenUpdating = False      'Prevent screen flickering while doing the refresh
'''''''''''''''''''''''''''''''''''''''
 ''''''''''''Call sub procedure'''''''''    
Call ClearTableContents
 ''''''''''''''''''''''''''''''''''''
mySheet.Activate

Application.ScreenUpdating = True       'Prevent screen flickering while doing the refresh


''''''''Show User id and Password box'''''''''
If Len(Uid) < 1 Or Len(Password) < 1 Then
    UserForm1.Show
End If

If (cancelEvent = True) Then
    Exit Sub
End If
............
............perform some task with error handling

Below is the code base of the called Sub

 Sub ClearTableContents()
 Dim wrksht As Worksheet
 Dim objListObj As ListObjects
 Dim tableName As String
 Dim ActiveTable As ListObject
 Dim rowCount As Integer
 Dim colCount As Integer
 Dim i As Integer
 Dim j As Integer


 '''''Iterate through the Bdv1, bdv2 and Match sheets. Set default table sizes for each 
 sheet'''''''''
 For j = 2 To 4
    If (j = 2) Or (j = 3) Then
        rowCount = 5
        colCount = 6
    ElseIf (j = 4) Then
        rowCount = 5
        colCount = 9
    End If

    Application.ScreenUpdating = False      'Prevent screen flickering while doing the refresh

    Set wrksht = ActiveWorkbook.Worksheets(j)

    Set objListObj = wrksht.ListObjects     'Get list of tables objects from the current sheet

'''''''Iterate through the tables in the active worksheet''''''''''''''
    For i = 1 To objListObj.Count
        tableName = objListObj(i).Name
        Set ActiveTable = wrksht.ListObjects(tableName)
        On Error Resume Next

''''''For each table clear the contents and resize the table to default settings''''''''''''
        With wrksht.ListObjects(i)
            .DataBodyRange.Rows.Clear
            .Range.Rows(rowCount & ":" & .Range.Rows.Count).Delete

            .HeaderRowRange.Rows.ClearContents
            .HeaderRowRange.Rows.Clear
            .Range.Columns(colCount & ":" & .Range.Columns.Count).Delete

            .Resize .Range.Resize(rowCount, colCount)
        End With
        wrksht.Columns("A:Z").AutoFit

    Next i
Next j

ThisWorkbook.Worksheets(2).Activate '''set the active sheet to the sheet number 2

Application.ScreenUpdating = True      'Prevent screen flickering while doing the refresh

Exit Sub
'Error Handling
NoTableSelected:
  MsgBox "There is no Table currently selected!", vbCritical

End Sub

Please help in resolving the issue. If I execute as independent macro on click of the button, it works perfectly well.

Not sure what line your code is hitting the error, but my immediate suspect is relying on `ActiveSheet` and `Activate`. These are absolutely not needed, especially if you've disabled screen updates. See [avoiding the use of Select and Activate](https://stackoverflow.com/a/10718179/4717755) — PeterT, Mar 24 '20 at 18:55
also, remove `On Error Resume Next` and then debug the code line-by-line, inspect variables/values, etc. You'll surely find your issue that way. If not, create a [mcve] for us once you hone in on the issue. Right now it's a code dump and you're asking us to decipher a lot — Scott Holtzman, Mar 24 '20 at 19:35

score 0 · Accepted Answer · answered Mar 24 '20 at 19:40

I am going to post this as an "answer", since I think it may at least help, if not solve, your issue.

Clearing tables (list objects) via VBA code can be a little tricky, and I learned this hard way. I developed and have been using the below function for quite some time and it works like a charm. There are comments to explain the code in the function.

Sub clearTable(whichTable As ListObject)

    With whichTable.DataBodyRange

        'to trap for the bug where using 'xlCellTypeConstants' against a table with only 1 row and column will select all constants on the worksheet - can't explain more than that its a bug i noticed and so did others online
        If .rows.count = 1 And .columns.count = 1 Then
            If Not .Cells(1, 1).HasFormula Then .Cells(1, 1).ClearContents
        Else

            'my tables often have formulas that i don't want erased, but you can remove if needed
            On Error Resume Next
            .SpecialCells(xlCellTypeConstants).ClearContents
            On Error GoTo 0

        End If

        'remove extra rows so table starts clean
        Dim rowCount As Long
        rowCount = .rows.count
        If rowCount > 1 Then .rows("2:" & rowCount).Delete 'because you can't delete the first row of the table. it will always have 1 row

    End With

End Sub

Call the procedure like this:

Dim lo as ListObject
For each lo in Worksheets(1).ListObjects
     clearTable lo
next

score 0 · Answer 2 · answered Mar 25 '20 at 09:22

0

Commented line to make my code work .Range.Columns(colCount & ":" &
.Range.Columns.Count).Delete

answered Mar 25 '20 at 09:22

user13098199

5
2

Table refresh vba excel Call procedure from another procedure Error Code 1004

2 Answers2