kaidobor

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?

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?