View Full Version : Delete then restore all data tables in a workbook - fix for Excel 2010?

08-29-2014, 06:34 AM
I found a very useful macro for speeding up a complex model file, which has data tables.
I cannot post a link, but search for "Delete then restore all data tables in a workbook" to find a macro in vbaexpress.

The problem is that the macro works in original XLS file but not in XLSM (I saved as XLSM).

I am not very familiar with VBA, but I can quickly say that error "13 Type mismatch" pops up and debugging highlights following line
If X(Application.Min(i + 1, UBound(X, 1)), 1) = X(i, 1) And Range(X(Application.Min(i + 1, UBound(X, 1)), 2)).Row - Range(X(i, 2)).Row <= 1 And i <> UBound(X, 1) Then
Set Newrange = Union(Newrange, Range(X(i + 1, 2)))

And types of variables are following:
Dim X() As Variant
Dim i As Long
Dim Myrange As Range, Newrange As Range

Is there a simple reason, why the 2007 file format fails?

08-29-2014, 07:07 AM
this kb article:

i downloded the file, saved as xlsm, clicked Remove Data Tables Button, clicked Restore Data Tables.
i did not duplicate the problem.

did you follow the same steps?

08-29-2014, 07:20 AM
I checked once again. It appears that Excel does not immediately switch to new file format after doing Save As (I've noticed it earlier in other situations). Rather you then have to close the file and open the XLSM again. Then the problem appears. I checked also that restoring data tables works in XLSM. Problem is only in removing.

08-29-2014, 10:31 AM
yes, when i tested with excel 2010 in the office i duplicated the problem. i didnot review the entire coding bat the reason is it filled the ~900K of rows with N/A in DataTableMap sheet. it actually has 94 rows of data.

but now i tested it with excel 2013 at home, and there is no problem.

08-29-2014, 02:13 PM
I got new power from your response, so looked further into the problem and suddenly fixed it. The problem was in another place.

Sub MapDataTables() function was the culprit.
It defines an array:
Dim X(1 To 65536, 1 To 4)
and stores information in this array.

Now seems like the failure is in next step:
newWs.Range("A:D") = X
The ranges does not match the array size. And this seems to create a bunch of N/A-s. Specifically, there is 64 rows of data, 65536 - 64= 65 472 empty rows and 1048576 - 65536 = 983 040 rows of N/A-s. The

The function starts to work, when the "pasting" gets specified as
newWs.Range("A1:D65536") = X

I also tried to define X as
Dim X(1 To 1048576, 1 To 4)
, but this slows the function down, though works perfectly. For sensibility could use something even more reasonable, since it is hard to believe anyone has a file with data tables with more than few thousand cells.

It is weird, how Excel 2013 handles this differently.

Ayway, thanks for pushing me to get to the computer once more to check for solution :)