0

I opened an Excel-Sheet I wanted to load and manually counted Rows and Columns, because my App always crashed. I then used:

rows = 3114; //manually counted
columns = 29 //manually counted
calcRows = ws.UsedRange.Rows.Count;
calcColumns = ws.UsedRange.Columns.Count;

After running the code, it showed:

rows =  3114  
columns = 29   
calcRows =  3117
calcColumns = 30

I couldn't find any data in the 3 more rows which were calculated or the additional column. Only after deleting those rows the method calculated the right way. So maybe there were cells already used but the content was deleted - and the mentioned method counts those

Since the Sheet is manually managed by the enduser and new data are manually added, I cannot be sure the counting is right this way. Is there a better way?

I read about:

usedCol = ThisWorkbook.ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column
usedRow = ThisWorkbook.ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

But my App does not understand "xlCellTypeLastCell".

maccettura
  • 10,514
  • 3
  • 28
  • 35
HalbeSuppe
  • 47
  • 5
  • Try using 11 instead of xlCellTypeLastCell. –  Apr 06 '18 at 20:19
  • one of the many reasons I refuse to accept excel sheets as a data transfer format. I've run into the same issue and ended up going through each row until I found more than 100 without any data in the cells, then cut off the last 100. Not a good solution, but then again neither is excel in the first place. – Broom Apr 06 '18 at 20:30
  • The Used Range can also include cells where formatting may have been changed, so that could be the issue. And sometimes Excel just plain gets confused, and you can sometimes get it to recalibrate merely by using the UsedRange property itself. Is there any change if you use the property twice? The first time to see if it causes Excel to rethink, and the second time to get the rethunk answer... – jeffreyweir Apr 06 '18 at 20:40
  • UsedRange is not reliable for what you want to do. See @ShaiRado's answer to [excel vba select last row and last column](https://stackoverflow.com/questions/43353218/excel-vba-select-last-row-and-last-column) for a robust method (ignore the accepted answer) to determine `LastRow` and `LastColumn` – Ron Rosenfeld Apr 07 '18 at 01:32

0 Answers0