Pivot Cache Type Mismatch error VB.NET Excel

Please see bottom edit for where I am currently at, thank you.

I have created a pivot table that works fine when the pivot cache is defined as:

        Dim ptCache As Excel.PivotCache = mainHighway.PivotCaches.Add(SourceType:=Excel.XlPivotTableSourceType.xlDatabase, SourceData:=mainHighwayData.Range("a1:v7500"))

My problem is that the number of rows changes from day to day, so I figure out the number of rows in the worksheet and then use that in my pivot cache:

Dim highlRow As Integer
highlRow = mainHighwayData.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Row
Dim ptCache As Excel.PivotCache = mainHighway.PivotCaches.Add(SourceType:=Excel.XlPivotTableSourceType.xlDatabase, SourceData:=mainHighwayData.Range("a1:V" & highlRow))

This solution has worked great for me historically, but when I do it here, I get:

Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))

I have been fiddling around with it a fair bit and found that if I do:

highlRow = 7000
Dim ptCache As Excel.PivotCache = mainHighway.PivotCaches.Add(SourceType:=Excel.XlPivotTableSourceType.xlDatabase, SourceData:=mainHighwayData.Range("a1:V" & highlRow))

It works fine, so I am left wondering what is the problem with how I define highlRow using the specialcells command and store it as an integer. I feel like there is some kind of type or casting issue here, but I can't put my finger on it. I tried:

highlRow = CType(mainHighwayData.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Row, Integer)

And that was no good either. Perhaps my method for finding the last row is just not applicable here? Anyway, I have spent a long time tinkering with it and I am just spinning my wheels at this point. If you have any thoughts it would be much appreciated.

Thank you as always SO.

edit: I also tried out:

UsedRange.Rows.Count

Which has worked historically and it was also not able to run. At this point I am very curious as to why pivot cache specifically seems to have this issue.

@charles had a good idea to try other ways of getting the last cell. I tried:

highlRow = mainHighwayData.Range("A65535").End(XlDirection.xlUp).Row

I got it to give the correct rows, but it still throws the type mismatch error.

highlRow = mainHighwayData.Cells.Find("*", SearchOrder:=Excel.XlSearchOrder.xlByRows, SearchDirection:=XlSearchDirection.xlPrevious).Row

This returns the proper number for the last row, but once again throws the same type mismatch error.

Edit: I found out another bit of information, but I am not sure what to do with it. The pivot table works fine if the values in it are <= 65536, but the second I increase the range to 65537 I get the type mismatch that has been haunting me. This is true for all numbers >= 65537. I know that 65535 or there abouts used to be the last row in excel, but that is no longer the case. Also when I create the pivot table manually in excel I have no trouble and it has all of the data. I am using int or long, so it should not be an overflow or anything. Anyone have any thoughts on why VB.NET will not let me make a pivot table based on data with more than 65537 rows?

Well it was a bit of a nightmare, but I found a fix that works. I noticed that people in vba have the same trouble and they were switching to r1c1 notation, but I couldn't get that working either, but I found this post and the last comment on the answer there had the solution.

highlRow = mainHighwayData.Range("A500000").End(XlDirection.xlUp).Row
Dim rng As Excel.Range
rng = mainHighwayData.Range("A1:Q" & highlRow)
mainHighway.Names.Add(Name:="Range1", RefersTo:=rng)
Dim ptCache As Excel.PivotCache = mainHighway.PivotCaches.Add(SourceType:=Excel.XlPivotTableSourceType.xlDatabase, SourceData:="Range1")

So you create a named range and then refer to it. I guess the idea is you need to abstract away from simple range objects and that will allow you to make your pivot table.