Создание сводной таблицы из массива более 65536 строк на VBA

@RomanVN · Регистрация: 26.11.2018

Студворк — интернет-сервис помощи студентам

Прошу помочь со следующей дилеммой.
Написал макрос для обработки массива базы данных с формирование сводной таблицы.
При размере базы данных менее 65536 строк макрос работает без проблем.
При превышении указанной размерности марос при формировании сводной таблицы выдает ошибку.

На листе Data содержатся исходные данные для обработки ̣диапазона (B2:ilastcoumnilastrow)

Visual Basic

Sub Macrọ()
Dim Pivot As Worksheet
 
Set Pivot = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(1)): Pivot.Name = "Pivot'
 
With Sheets("Data").Select
   ilastrow = Cells(Rows.Count, 2).End(xlUp).Row
   ilastcolumn = Cells(2, Columns.Count).End(xlToLeft).Column
   Set PivotAll_Data = Worksheets("Data").Range(Cells(2, 2), Cells(ilastrow, ilastcolumn))
End With
 
ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PivotAll_Data).CreatePivotTable TableDestination:=Pivot.Cells(2, 2), TableName:="Pivot"
 
End Sub()

Насколько я понимаю ошибка 13 ̣̣(type mismatch) появляется на этапе передачи исходного диапазона для формирования сводной таблицы.
Много гуглил, но предлагаемые решения не смог применить. Потратил много времени и теперь решил обратиться за советом к экспертам.

Спасибо за помощь!

@Hugo121 · 26.11.2018, 19:52

With Sheets("Data").Select
End With
вообще лишнее, ибо никак не используете.
Может это и есть причина ошибки, кто знает...

Хотя если этот лист всёж реально активируется - то некое влияние это оказывает, опосредованно...

@snipe · 27.11.2018, 01:00

ограничения в 65536 строк были в старых офисах и файлах типа xls
в файлах xlsx xlsm и более молодых офисах - начиная с 2007 эта цифра увеличилась

@RomanVN · 27.11.2018, 04:10 **[ТС]**

Сам спросил - сам ответил.

Sheets("data").select выделяется, т.к. в процессе макроса идет переключения между листами для работы с именованными диапазонами. Эксель у меня стоит 2013, формат кинги banary workbook.

Visual Basic

With Sheets("Data").Select
   ilastrow = Cells(Rows.Count, 2).End(xlUp).Row
   ilastcolumn = Cells(2, Columns.Count).End(xlToLeft).Column
   PivotAll_Data = Range(Cells(2, 2), Cells(ilastrow, ilastcolumn))[B][U].address[/U][/B]
End With

Всем поучаствовавшим - спасибо

@Hugo121 · 27.11.2018, 11:17

Выделение листа/диапазона - лишнее действие, жрущее время и деньги...

Visual Basic

With Sheets("Data")
   ilastrow = .Cells(.Rows.Count, 2).End(xlUp).Row
   ilastcolumn = .Cells(2, .Columns.Count).End(xlToLeft).Column
   PivotAll_Data = Range(.Cells(2, 2),.Cells(ilastrow, ilastcolumn)).address
End With

@Hugo121 6919 / 2829 / 543 Регистрация: 19.10.2012 Сообщений: 8,644
	26.11.2018, 19:52	2
	With Sheets("Data").Select End With вообще лишнее, ибо никак не используете. Может это и есть причина ошибки, кто знает... Хотя если этот лист всёж реально активируется - то некое влияние это оказывает, опосредованно... 0

@snipe 4044 / 1430 / 394 Регистрация: 07.08.2013 Сообщений: 3,571
	27.11.2018, 01:00	3
	ограничения в 65536 строк были в старых офисах и файлах типа xls в файлах xlsx xlsm и более молодых офисах - начиная с 2007 эта цифра увеличилась 0

Опции темы