Excel VBA - удалить пустые строки в таблице

Я пытаюсь запустить макрос, который выбирает пустые ячейки в столбце таблицы и удаляет всю строку.

Сценарий ниже выполняет все, кроме удаления части, которая вызывает следующую ошибку:

run-time error 1004 - "Delete method of Range class failed"

Я использовал следующий код:

Sub test()
Range("Table1[[New]]").Activate
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
End Sub

Ответы

Ответ 1

Хороший вопрос! Без таблицы .EntireRow.Delete всегда работает, но внутри таблицы это выглядит так, как нет.

Это работает:

Sub Test()
  Dim Rng As Range
  On Error Resume Next
  Set Rng = Range("Table1[[New]]").SpecialCells(xlCellTypeBlanks)
  On Error Goto 0
  If Not Rng Is Nothing Then
    Rng.Delete Shift:=xlUp
  End If
End Sub

Ответ 2

Вы действительно можете сделать это за один проход, но вам нужно использовать объект ListObject и его свойства DataBodyRange и ListColumns:

Sub ClearBlankCellsInColumnNew()
Dim rngBlanks As Excel.Range

With Worksheets("Sheet1").ListObjects("Table1")
    On Error Resume Next
    Set rngBlanks = Intersect(.DataBodyRange, .ListColumns("New").Range).SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0
    If Not rngBlanks Is Nothing Then
        rngBlanks.Delete
    End If
End With
End Sub

Ответ 3

Шаг 1: Создайте вспомогательный столбец в таблице, где вы проверяете наличие пустых полей в этой строке. Например, если в вашей таблице было 3 столбца: A (Цена), B (Количество) и C (Стоимость), вы бы добавили четвертый столбец D и назовите его "Любые пробелы?". Уравнение будет =IF(OR(ISBLANK([@Price]),ISBLANK([@Quantity]),ISBLANK([@Cost])),"Yes","No")

Это даст вам столбец для фильтрации, чтобы просмотреть все пробелы.

Шаг 2. В VBA вы выполните следующие действия:

Range("MyTableNameHere").AutoFilter Field:=Range("MyTableNameHere[Any Blanks?]").Column, Criteria1:="Yes"
Application.DisplayAlerts = False
Range("MyTableNameHere").ListObject.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
Application.DisplayAlerts = True
Range("MyTableNameHere").AutoFilter Field:=Range("MyTableNameHere[Any Blanks?]").Column

Это по существу фильтрует строки, которые вы хотите удалить в таблице, используя вспомогательный столбец, выбирая все видимые данные в таблице и отфильтровывая таблицу. Я искал, как удалить все видимые строки в таблице, и нашел это и пошатнулся, пока не понял, что это сработает. Взятие этого и объединение его со вспомогательным столбцом для выбора всех строк с любыми пробелами похоже на то, что вы тоже хотели.

Ответ 4

Адаптация предыдущих ответов:

On Error Resume Next
Set Rng = ListObjects(1).DataBodyRange.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not Rng Is Nothing Then
Rng.Delete Shift:=xlUp
End If

Ответ 5

Использование ListObjects в Excel позволяет легко использовать следующие элементы для удаления пустых строк.

Sub RemoveBlankRow(ByVal SheetName As String, TableName As String)
Dim rng As Integer

rng = Sheets(SheetName).ListObjects(TableName).DataBodyRange.Rows.Count

For i = 1 To rng
    If Application.WorksheetFunction.CountA(Rows(i)) = 0 Then Rows(i).EntireRow.Delete
Next
End Sub

Ответ 6

Две заметки о решении Фрейда Линдстрема, которые я использовал, но немного подправил:

(1) добавить End If перед следующим

(2) add "i = i - 1" just before the End If

Why?, потому что если у вас есть пустые строки одна над другой, вы пропустите одну, поскольку номера всех строк просто сдвинулись на одну. По сути, если вы удалили row [N], другой ряд теперь называется row [N], и вам также нужно проверить его, а не переходить сразу к row [N + 1].

БОЛЬШАЯ ПЕРЕДАЧА: если ваш последний ряд пуст, это даст вам застрявшую петлю. Я, вероятно, добавлю IF, чтобы справиться с этим, хотя.

Я знаю, что это старая ветка, но подумал, что добавлю это на тот случай, если кто-нибудь еще найдет похожие решения. Спасибо Фрею - ваш код действительно помог!