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

Post the workbook on a file-sharing website or email it to me via the contact page on my website www.manville.org.uk and I will take a look.

Answer 3

I have tried this on every sheet and only come to the places where my entries stop before the hidden cells. Any other suggestions?

Answer 4

I tried the macro on every page and am still getting the error message. Any other suggestions?

Answer 5

Hi EBett

(make a copy first )

try this code to clear data starting from Row2004 and down

Sub myClear ()

ActiveSheet.Rows("2004:1048576").Clear

End Sub

[Edit]

if you want to clear  Columns e.g HZ up to IV

add this line:

ActiveSheet.Columns("HZ:IV").Clear