Compatibility Error Message

Question

I am receiving the following compatibility error message. How do I fix this?

"This workbook contains data in cells outside of the row and column limit of the selected file format. Data beyond 256 (IV) columns by 65,536 rows will not be saved. Formula references to data in this region will return a #REF! error."

Answer 1

For the record, EBrett emailed me a sample workbook.

It had rows beyond row 2003 hidden and columns beyond the first few hidden.

However, a non-default font had been applied to cells out to column XFC and it was this that was causing the compatibility warning.

I wrote a macro to:

a) change the Normal Style to the non-default font

and for each worksheet:

b) unhide the excess rows and columns

c) clear (probably not necessary) and delete the excess rows and columns

d) re-hide the excess rows and columns

e) report the new used range.

The result saved in 2003 format without compatibility warnings.

Sub TidyUp()

  Dim WS As Worksheet

  Dim lRow As Long

  Dim lCol As Long

  Dim lLast As Long

  Dim bHidden As Boolean

  With ActiveWorkbook.Styles("Normal").Font

    .Name = "Times New Roman"

    .Size = 12

  End With

  For Each WS In ActiveWorkbook.Worksheets

    lLast = 1

    For lRow = 1 To 65536

      If WS.Rows(lRow).Hidden Then Exit For

      If WorksheetFunction.CountA(WS.Rows(lRow)) > 0 Then lLast = lRow

    Next

    If lRow > 65536 Then

        lRow = lLast + 1

        bHidden = False

    Else

        bHidden = True

    End If

    Application.DisplayAlerts = False

    With WS.Rows(lRow).Resize(WS.Rows.Count - lRow + 1)

      If bHidden Then .Hidden = False

      .Clear

      On Error Resume Next

      .Delete

      On Error GoTo 0

    End With

    With WS.Rows(lRow).Resize(WS.Rows.Count - lRow + 1)

      .Style = "Normal"

      .Hidden = bHidden

    End With

    Application.DisplayAlerts = True

    For lCol = 1 To WS.Columns.Count

      If WS.Columns(lCol).Hidden Then Exit For

      If WorksheetFunction.CountA(WS.Columns(lCol)) > 0 Then lLast = lCol

    Next

    If lCol > WS.Columns.Count Then

        lCol = lLast + 1

        bHidden = False

    Else

        bHidden = True

    End If

    Application.DisplayAlerts = False

    With WS.Columns(lCol).Resize(, WS.Columns.Count - lCol + 1)

      If bHidden Then .Hidden = False

      .Clear

      On Error Resume Next

      .Delete

      On Error GoTo 0

    End With

    With WS.Columns(lCol).Resize(, WS.Columns.Count - lCol + 1)

      .Style = "Normal"

      .Hidden = bHidden

    End With

    Application.DisplayAlerts = True

    Debug.Print WS.Name, WS.UsedRange.Address

  Next

End Sub

Answer 2

It could be that there are formulas referencing cells outside the A1:IV65536 area and that is where the problem lies.

Next time I suggest you start by saving as 97-2003 and do all the work in compatibility mode - or better still work in the earliest version with which the workbook will be used, to ensure compatibility.

Answer 3

There has to be some data in a cell row 65536/column 256.

Try to press CTRL+END to jump to the intersection of last row/column with data.

Answer 4

There isn't any data in those columns or rows as they have been hidden since I started working on the project. I need to save it in 97-2003 so that people with those versions can use the sheet.

Any suggestions as to how data might have gotten into those rows or columns and/or how to find it and get it out?

Answer 5

Either:

•  don't save to 97-2003 format (xls) but rather to 2007 format (xlsx)

Or

• reorganise your worksheet so that there is no data to the right of column IV or below row 65536.