excel - 当有更多数据时，使用 vba 在 excel 中创建数据透视失败

Question

我的 Excel：-

我创建 Pivot 的 VBA 代码：-

Sub Macro10()
'
' Macro10 Macro
'
' Keyboard Shortcut: Ctrl+q
'
    Application.CutCopyMode = False
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R20C4", Version:=6).CreatePivotTable TableDestination:= _
        "Sheet2!R3C1", TableName:="PivotTable3", DefaultVersion:=6
    Sheets("Sheet2").Select
    Cells(3, 1).Select
End Sub

上面创建了 Pivot，但是当 excel 中有更多/更少的行时，它会给出错误。

标签： excelvba

Answer 1

要动态查找工作表中最后使用的行，请尝试以下操作。

Ways To Find The Last Row

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

要查找最后一列，请尝试以下操作。

Ways To Find The Last Column

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

资源：

https://www.thespreadsheetguru.com/blog/2014/7/7/5-different-ways-to-find-the-last-row-or-last-column-using-vba

此外，打开宏记录器并单击您需要执行的步骤。将为您生成所有必要的代码。您将获得比您需要的更多的代码，但这很好，因为您可以简单地删除不需要的部分。使用鼠标，在宏代码内单击并反复按 F8 键以逐行浏览代码。这样做，你会学到很多东西！对您不了解的内容进行一些智能 Google 搜索，以填补空白。几天后，您将成为这方面的专家。

这是一个很好的链接供您开始使用。

https://www.contextures.com/excel-vba-pivot-table-field-list.html