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

Question

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

Сценарий ниже делает все, кроме удаления части, которая запрашивает 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

Answer 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

Answer 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

Answer 3

Этот лайнер также поможет

при ошибке возобновить следующий ', чтобы продолжить макрос, если в таблице не найдено пустой строки.Range("Table1").Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

'это удалить пусто, используя 1-й столбец в качестве справки для проверки пробелов' Использовать собственные имена для таблиц для упрощения манипуляций с кодами

Answer 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

Answer 5

Шаг 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

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

Answer 6

Использование 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

Answer 7

Это мое решение:

      Public Sub remove_blank_rows(sh As Worksheet)

    Dim tbl As ListObject
    Set tbl = sh.ListObjects(1)
    Dim i As Long
    Dim ii As Long
    
    Application.EnableEvents = False
    
    ii = tbl.Range.rows.Count
    For i = 2 To ii
        If WorksheetFunction.CountA(rows(i)) = 0 Then
            
            rows(i).EntireRow.Delete
            
            ii = tbl.Range.rows.Count
            If i >= ii Then
                GoTo A
            End If
            
            If i > 1 Then
                i = i - 1
            End If
    
        End If
    Next
A:
    
    Application.EnableEvents = True
End Sub

Обратите внимание, что когда вы удаляете строку, все строки под ней сдвигаются вверх на 1, поэтому индекс уменьшается, чтобы снова проверить ту же строку на следующей итерации! Однако обратите внимание, чтоiне нужно выходить за определенные пределы, и это делается с помощью двухIfпредложения.

Answer 8

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

(1) добавьте End If перед Next

(2) добавьте "i = i - 1" непосредственно перед End If

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

BIG CAVEAT: если ваша последняя строка пуста, это приведет к зависанию цикла. Я, вероятно, поставлю IF, чтобы справиться с этим.

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