Range.Sort throws an error even though cells are not merged

By | March 9, 2017

Recently, we encountered an issue in our production environment where so-far-nicely-working complex spreadsheet threw an exception on Range.Sort. The error description was “to do this all merged cells should be of same size”. Unfortunately there was no merged cell and Excel was still complaining about merged cell. Finally I figured out that the range contained only one cell to be sorted and hence it was throwing an error (weird hmmm). Once I bypassed the sorting when the range contained only 1 cell, everything started working again.

Possible Cause

I believe that when the range contains only 1 cell, Excel is trying “detect” the nearby filled cells (try it yourself. Select any one cell and click on sort button. As soon as the Sort dialog opens up, Excel will highlight nearby columns and rows to perform Sort action.) and if any one of them contains merged cell, it throws this error. When there are more than 1 cells, Excel will sort only on the given range and hence Range.Sort works

Vishal Monpara is a full stack Solution Developer/Architect with 12 years of experience primarily using Microsoft stack. He is currently working in Retail industry and moving 1’s and 0’s from geographically dispersed hard disks to geographically dispersed user’s mind leveraging geographically dispersed team members.

Leave a Reply

Your email address will not be published. Required fields are marked *