删除以VBA中列内容为条件的行 [英] Deleting rows conditional on the content of a column in VBA

问题描述

我是VBA的初学者，所以我可能犯了非常基本的错误.我想删除工作簿的每个工作表中的所有行，其中该行在S列中没有条目.

I'm a beginner in VBA so I'm probably making very elementary mistakes. I want to delete all rows in each of the worksheets of a workbook where the row has no entry in column S.

我已经使用以前回答的问题得出的一些见解编写了以下代码，但未按预期工作:

I have written the following bit of code using some insights from previously answered questions, but it is not working as expected:

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
For Each cell In Range("s1:s400")
    If IsEmpty(cell) Then
    cell.EntireRow.Delete shift:=xlUp
    End If
Next
Next

根本没有遵循第一个循环.仅活动工作表已删除任何行.第二个循环的应用不一致，因为并非所有S列中具有空单元格的行都将被删除.

The first loop is not being followed at all. Only the active sheet has any rows deleted. The second loop is applied inconsistently, as not all rows with empty cells in the S column are being deleted.

感谢您提供的任何帮助.

Thank you for any help you can provide.

推荐答案

两件事.

首先，您需要将工作表分配给范围对象，因此 Range("s1:s400")应该为 ws.Range("s1:s400")

First you need to assign the sheet to the range object so Range("s1:s400") should be ws.Range("s1:s400")

第二，在循环中删除行时，向后循环.无法在For Each循环中完成此操作，因此请更改为常规的for循环和 Step -1

Second when deleting rows in a loop, loop backwards. This cannot be done in a For Each loop so change to a regular for loop and Step -1

Dim ws As Worksheet
Dim i As Long

For Each ws In ThisWorkbook.Worksheets
    For i = 400 To 1 Step -1
        If ws.Cells(i, "S").Value = "" Then
            ws.Rows(i).Delete
        End If
    Next
Next

有关删除行的更多更快的方法，请参见

For more and faster methods on deleting rows see HERE.

这篇关于删除以VBA中列内容为条件的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！