Макрос Excel VBA Создание сводной таблицы с динамическим диапазоном данных: ошибка диапазона исходных данных

Question

Я новичок в кодировании VBA и пытаюсь создать макрос Excel, который будет создавать сводную таблицу на основе динамического диапазона данных. Я использую Excel 2016

Количество столбцов в данных останется неизменным, но количество строк будет динамическим.

В моей таблице данных столбец BZ содержит количество записей в наборе данных. Например, если в наборе данных 30 записей, каждое значение в столбце BZ будет равно 30. (Эти данные импортируются из SAS с использованием DDE, поэтому я выбрал этот метод). Поэтому я добавил 1 в переменную lastRow ниже, чтобы включить имена столбцов.

Мой код макроса VBA в настоящее время выглядит следующим образом:

Sub Macro1()
   Dim xlsPath As String
   Dim xlsFile As String
   Dim lastRow As Integer
   Dim SrcData As String
   Dim StartPvt As String
   Dim pvtCache As PivotCache
   Dim pvt As PivotTable

   xlsxPath = "N:\Analytics\Test\DDE\"
   xlsxFile = "Test.xlsx"

   lastRow = Sheets(1).Range("BZ2") + 1

   SrcData = ActiveSheet.Name & "!" & Range("A1:R" & lastRow & "C77").Address(ReferenceStyle:=xlR1C1)

   StartPvt = sht.Name & "!" & sht.Range("A3").Address(ReferenceStyle:=xlR1C1)

   Workbooks.Open Filename:=xlsxPath & xlsxFile

   Range("A1:BY" & lastRow).Select

   Sheets.Add

   Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
   SourceType:=xlDatabase, _
   SourceData:=SrcData, Version:=6)

   Set pvt = pvtCache.CreatePivotTable( _
   TableDestination:=StartPvt, _
   TableName:="PivotTable1", DefaultVersion:=6)

   Columns("BZ:BZ").Select
   Selection.Delete Shift:=xlToLeft
   ActiveWorkbook.Save
   ActiveWorkbook.Close
End Sub

Это приводит к ошибке, которая говорит:

Ошибка во время выполнения "1004":

Метод 'Range' для 'object'_Global' не удался

Отладчик указывает на строку, где определяется диапазон SrcData. Я думаю, что проблема может быть в синтаксисе, где я указал lastRow в Range.

Решение для этой строки кода или любые другие полезные комментарии будут с благодарностью.

Answer 1

Вы можете включить средство записи макросов, затем щелкнуть в любом месте набора данных, и Excel автоматически выберет последний использованный столбец и последнюю использованную строку в этом наборе данных непосредственно перед созданием сводной таблицы. Попробуйте, и вы увидите. Если вы хотите использовать VBA для динамического выбора последней строки и динамического выбора последнего столбца, ознакомьтесь с приведенным ниже кодом.

Способы найти последнюю строку

Sub FindingLastRow()
'PURPOSE: Different ways to find the last row number of a range
'SOURCE: www.TheSpreadsheetGuru.com

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ActiveSheet

'Using Find Function (Provided by Bob Ulmas)
  LastRow = sht.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

'Using SpecialCells Function
  LastRow = sht.Cells.SpecialCells(xlCellTypeLastCell).Row

'Ctrl + Shift + End
  LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

'Using UsedRange
  sht.UsedRange 'Refresh UsedRange
  LastRow = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row

'Using Table Range
  LastRow = sht.ListObjects("Table1").Range.Rows.Count

'Using Named Range
  LastRow = sht.Range("MyNamedRange").Rows.Count

'Ctrl + Shift + Down (Range should be first cell in data set)
  LastRow = sht.Range("A1").CurrentRegion.Rows.Count

End Sub

Способы найти последний столбец:

Sub FindingLastColumn()

'PURPOSE: Different ways to find the last column number of a range
'SOURCE: www.TheSpreadsheetGuru.com

Dim sht As Worksheet
Dim LastColumn As Long

Set sht = ThisWorkbook.Worksheets("Sheet1")

'Ctrl + Shift + End
  LastColumn = sht.Cells(7, sht.Columns.Count).End(xlToLeft).Column

'Using UsedRange
  sht.UsedRange 'Refresh UsedRange
  LastColumn = sht.UsedRange.Columns(sht.UsedRange.Columns.Count).Column

'Using Table Range
  LastColumn = sht.ListObjects("Table1").Range.Columns.Count

'Using Named Range
  LastColumn = sht.Range("MyNamedRange").Columns.Count

'Ctrl + Shift + Right (Range should be first cell in data set)
  LastColumn = sht.Range("A1").CurrentRegion.Columns.Count

End Sub

Вы можете легко включить это в уже имеющийся у вас код.