Excel(2016) 32768th row limit

Hey guys,

Do anyone know how to overcome Excel 2016's 32768th row barrier? I am using macro and trying to filter cells/rows with background colour. My formula should return true or false but after 32768th row it is returning #NUM!. I have at least 100,000 rows in my spreadsheet.

Thanks!

here is: https://support.office.com/en-us/art...7-269d656771c3

Unless you're working with a book in Compatibility Mode (look at the title bar of the book)

Can you post the formula?
Better yet, attach the book.

No mate, it is the "Length for a relational PivotTable string" i think.

I am trying to learn. I must say I am a biomedical scientist and not an expert in Excel.

I will take your comment as your way to help me
Have a good day Sandy.

Capture.PNG

so i have this module:

Funtion Cellcolour(Irow as Integer, Icol as Integer) as Long
Cellcolour = Cells(Irow, Icol).Integer.Colourindex
End Function


then in the formula bar for the column:
= If(Cellcolour(32768,2) = 20, "True", "Irrelevant")


it worked on the previous rows before 32768...

The link is the basis, the conclusions depend on you

Attach a sample workbook (not a picture!). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Originally Posted by Jonmo1

Unless you're working with a book in Compatibility Mode (look at the title bar of the book)

Can you post the formula?
Better yet, attach the book.

It is the Microsoft Excel Macro-Enabled Worksheet and I am using the latest 2016 Office.

Change your varibales from Integer to Long, as suggested in post #5.

and I am using the latest 2016 Office.

The version you are using doesn't impact whether you're in compatibility mode or not.
What matters, is which version the file you're working with was saved as.

Using 2016, opening a file saved in XL97-2003 format = compatibility mode.