Excel Crashes after VBA adds rows to table

[kscarlett5683]

Hi, I have searched everywhere and cannot find a good answer to my problem. I am writing a VBA macro that, amongst other things, gets data from another workbook and then adds rows to the primary workbook's table. I have isolated the problem to literally just expanding rows to a table -- my code works fine without this step. Additionally, just running the expanding rows code crashes Excel. The script will add the rows, but as soon as you save or leave Excel, it shuts down and restarts automatically. My code is below. Does anyone have any idea how to fix this? I am using Windows 10 and Office 365. Excel 64 bit. Thank you

counterADR = 12
If counterADR > 0 Then
'resize the table instead of adding rows. Adding rows crashes the script.
tblTracker.Resize tblTracker.range.Resize(tblTracker.range.Rows.count + counterADR)
End If
startingRow = tblTracker.range.Rows.count - counterADR


P.S. The workbook I am using is quite small.

 

[Domenic]

It runs fine for me without crashing Excel. Also, adding a ListRow as apposed to resizing shouldn't be a problem either...

'get table from sheet1
Dim tblTracker As ListObject
Set tblTracker = ThisWorkbook.Worksheets("Sheet1").ListObjects(1)

'add a listrow to the end of the table
Dim currentListRow As ListRow
Set currentListRow = tblTracker.ListRows.Add

'enter a value into the first cell of the new listrow
currentListRow.Range(1, 1).Value = "x"

It might help if you posted the complete code.

 

[kscarlett5683]

It runs fine for me without crashing Excel. Also, adding a ListRow as apposed to resizing shouldn't be a problem either...

'get table from sheet1
Dim tblTracker As ListObject
Set tblTracker = ThisWorkbook.Worksheets("Sheet1").ListObjects(1)

'add a listrow to the end of the table
Dim currentListRow As ListRow
Set currentListRow = tblTracker.ListRows.Add

'enter a value into the first cell of the new listrow
currentListRow.Range(1, 1).Value = "x"

It might help if you posted the complete code.

Of course it does, that's the story of my life! LMAO The reason I didn't post the whole thing was because I literally isolated just that code, ran it, and Excel crashed. It's very strange.

The other reason is because that is A LOT of code! I have to take a report, modify to get rid of extra rows, and then turn the whole thing into a table. Once all that re-formatting is done, I do a compare. I delete rows in the primary workbook with data that doesn't exist in the report. I then add what isn't already in the primary workbook (which brings me to the problem code).

But, in looking at your code, it occurs to me I can be more efficient by using a loop and adding a new row that way -- prior to doing this I was getting out of memory issues A LOT, so I re-wrote from scratch trying to optimize. Let me try this and see what happens.

 

[Domenic]

Sound good ...

 

[kscarlett5683]

Sound good ...

So, that didn't work either. It literally crashed Excel. This is frustrating. This is the code I used (all variables and objects are properly set, so that is not the issue):

Dim newRow As listRow
For j = LBound(arrADRNotFound) To UBound(arrADRNotFound)
If Not IsEmpty(arrADRNotFound(j)) Then
Debug.Print "The Tracker ID is this: " & arrADRNotFound(j)
Set newRow = tblTracker.ListRows.Add
newRow.Range(1, 2).Value = arrADRNotFound(j) 'adds a new row with the assessment id
End If
Next j

 

[kscarlett5683]

I think I know what the issue is. I started with a fresh workbook and copied the table over to it. I then used that fresh workbook to run my code - and everything worked just fine with no crashing. There is a corruption somewhere. I guess I'll have to rebuild this from scratch and discard the old file.

 

[Domenic]

You could try to repair your workbook. So instead of clicking on Open when opening your workbook, click on the drop down arrow and select Open and Repair.