VBA On Error help

[gheyman]

I have code that is used to clear a table. Below I am showing my two codes, the code that clears the table ("ClearTable"), I incorporated into the first code. I am showing both because I don't know if that impacts what I am trying to do. or not. I can merge the two into one code if you think that is a better way to go. I dint separate them for any special reason.

I am clearing the table and then pasting in data from another table. But I am not pasting it over the first two columns in the second table. Those have formulas in them.

The code to clear the table works great - but only if there is data already in the table. if there isn't data in the second table and the code is run I was getting a debug error. (the table may be empty at times because I am using this on a Template for my users).

Here is what I tried.

Sub PopulateSQDScub()
'G Heyman DRS NIS

'Insert a blank row inbetween each set of ItemID Duplicates

'Clear table
    ClearTable

'Paste SQD to SQD Scrub

    Range("SQDRange1").Copy
        Range("C11").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

'Add Spaces inbetween each unique ItemID

    Dim i As Long

    Application.ScreenUpdating = False


    With ActiveSheet.ListObjects("TableSQDScrub").Range.Columns(4)
        For i = .Rows.Count + 1 To 3 Step -1
            Do
                i = i - 1
                If i = 2 Then Exit For
            Loop While .Cells(i) = .Cells(i - 1)
                .Rows(i).Insert
                i = i + 1
        Next
    End With

    Application.ScreenUpdating = True

End Sub

Sub ClearTable()
'G Heyman DRS NIS

'Clears the SQD Scrub table before populating with SQD data

On Error GoTo Code_Error
    DoCmd.SetWarnings = False

   With Sheets("SQD Scrub").ListObjects("TableSQDScrub")
      If .ShowAutoFilter Then
         If .AutoFilter.FilterMode Then .AutoFilter.ShowAllData
      End If
      With .DataBodyRange
         .Offset(1).Resize(.Rows.Count - 1).Delete
         .SpecialCells(xlConstants).ClearContents
      End With
   End With
   
Code_Error:
   Exit Sub
   
End Sub

Thanks for your time and help! Very much appreciated

 

[RoryA]

DoCmd is an Access object and shouldn't be in this code.

You haven't said what the error is, but I suspect a simple check for the Listrows.count property of the table might fix your problem.