none
Excel crashes when resizing the list object in VBA RRS feed

  • Question

  • I have a VBA project (xlsb) which is crashing when resizing the list object

    Public Sub ResizeAndDump(ByRef lso As ListObject, ByRef varOut As Variant)
            
        If (Not lso.DataBodyRange Is Nothing) Then
            'Output the data to sheet
            lso.DataBodyRange.Clear
        End If
        
        Dim rng As Range
        
        
        Set rng = lso.Range.rows(1)
        Dim newRng As Range
        Set newRng = rng.Resize(UBound(varOut, 1) - LBound(varOut, 1) + 2, UBound(varOut, 2) - LBound(varOut, 2) + 1)
        
        Call lso.Resize(newRng)
        
        Set rng = lso.Range.rows(2)
        Set newRng = rng.Resize(UBound(varOut, 1) - LBound(varOut, 1) + 1, UBound(varOut, 2) - LBound(varOut, 2) + 1)
        newRng.Resize(UBound(varOut, 1) - LBound(varOut, 1) + 1, UBound(varOut, 2) - LBound(varOut, 2) + 1) = varOut
        newRng.WrapText = False
    End Sub


    Appreciate any help in this case.

    Thanks

    Tuesday, February 11, 2020 11:42 AM

All replies

  • Untested but change the following to set rng as the entire ListObject Range rather than just a row of the ListObject and then resize it.

    When resizing a ListObject, the entire ListObject is resized; not just a row of the ListObject.

    'Set rng = lso.Range.rows(1)   'Delete this line

    set rng = Iso.range      'Use this line in lieu of above    Dim newRng As Range
        Set newRng = rng.Resize(UBound(varOut, 1) - LBound(varOut, 1) + 2, UBound(varOut, 2) - LBound(varOut, 2) + 1)


    Regards, OssieMac

    Sunday, February 16, 2020 11:31 AM