excel - 删除 LastRow 到 LastRow

Question

我有一个工作表，用户可以输入将要使用的行数（我有可以扩大或缩小表格大小的代码）。当用户输入的行数少于表中的当前行数时，我想删除所有未使用的行。我目前正在使用 LastRow。我在代码的开头和结尾都有一个 LastRow，所以我非常清楚要删除哪些行，但我无法正确使用宏。我认为代码应该如下所示：

LastRowFirst = Worksheets("Sheet1").Cells(Rows.Count, 3).End(xlUp).Row
LastRowNext = Worksheets("Sheet1").Cells(Rows.Count, 3).End(xlUp).Row

Range("B" & LastRowFirst : "AA" & LastRowNext).Delete

我正在使用的总代码如下：

    LastRowFirst = Worksheets("Ent. Description").Cells(Rows.Count, 3).End(xlUp).Row

Application.AutoCorrect.AutoFillFormulasInLists = True

    Dim targetSheet As Worksheet
    Dim targetSheetName As String
    targetSheetName = "Ent. Description"
    Set targetSheet = ThisWorkbook.Worksheets(targetSheetName)
   
    Dim newTableRows As Long
    Dim newTableRowsCellAdd As String
    newTableRowsCellAdd = "A1"
    newTableRows = targetSheet.Range(newTableRowsCellAdd).Value2

    Dim targetTable As ListObject
    Dim targetTableName As String
    targetTableName = "Table1"
    Set targetTable = targetSheet.ListObjects(targetTableName)

    Dim newTableRange As Range
    Set newTableRange = targetTable.Range.Resize(newTableRows + 1, targetTable.Range.Columns.Count)
    targetTable.Resize newTableRange
    
    Dim tableCell As Range
    Dim rowWithFormulas As Long
    rowWithFormulas = 1
    For Each tableCell In targetTable.ListRows(rowWithFormulas).Range
    
        If tableCell.HasFormula Then
        
            tableCell.Copy targetTable.ListColumns(tableCell.Column).DataBodyRange
        
        End If
    
    Next tableCell

    LastRowNext = Worksheets("ent. Description").Cells(Rows.Count, 3).End(xlUp).Row

如果您还有任何问题，请告诉我

标签： excelvba

Answer 1

我添加了用于清除范围内容和删除整行的代码。

我建议您Option Explicit在模块的顶部使用。

阅读代码注释并选择其中一种方法（清除或删除），两者都不需要

编辑：刚刚更改了.clearcontentswith.clear所以格式也被删除了

Public Sub ResizeTable()
    
    Application.AutoCorrect.AutoFillFormulasInLists = True

    Dim targetSheet As Worksheet
    Dim targetSheetName As String
    targetSheetName = "Ent. Description"
    Set targetSheet = ThisWorkbook.Worksheets(targetSheetName)
   
    Dim newTableRows As Long
    Dim newTableRowsCellAdd As String
    newTableRowsCellAdd = "A1"
    newTableRows = targetSheet.Range(newTableRowsCellAdd).Value2

    Dim targetTable As ListObject
    Dim targetTableName As String
    targetTableName = "Table1"
    Set targetTable = targetSheet.ListObjects(targetTableName)
    
    ' Find the table's last row before resizing
    Dim previousLastRow As Long
    previousLastRow = targetTable.HeaderRowRange.Row + targetTable.Range.Rows.Count - 1

    Dim newTableRange As Range
    Set newTableRange = targetTable.Range.Resize(newTableRows + 1, targetTable.Range.Columns.Count)
    targetTable.Resize newTableRange
    
    ' Find the table's last row after resizing
    Dim newlastRow As Long
    newlastRow = targetTable.HeaderRowRange.Row + targetTable.Range.Rows.Count
    
    ' Clear range contents and formatting
    targetSheet.Range(targetSheet.Cells(newlastRow, targetTable.Range.Cells(1).Column).Address, targetSheet.Cells(previousLastRow, targetTable.Range.Cells(targetTable.Range.Columns.Count).Column).Address).Clear
    
    ' Delete all rows in range
    targetSheet.Range(targetSheet.Cells(newlastRow, targetTable.Range.Cells(1).Column).Address, targetSheet.Cells(previousLastRow, targetTable.Range.Cells(targetTable.Range.Columns.Count).Column).Address).EntireRow.Delete
    
    Dim tableCell As Range
    Dim rowWithFormulas As Long
    rowWithFormulas = 1
    For Each tableCell In targetTable.ListRows(rowWithFormulas).Range
    
        If tableCell.HasFormula Then
        
            tableCell.Copy targetTable.ListColumns(tableCell.Column).DataBodyRange
        
        End If
    
    Next tableCell
    
End Sub

试一试，让我知道它是否有效。