CODEWITHSUNDEEP
vbaexcel
SmonJunior
SmonJunior

Reputation: 13

Deleting rows conditional on the content of a column in VBA

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

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.

Upvotes: 1

Views: 953

Answers (1)

Scott Craner
Scott Craner

Reputation: 152495

Two things.

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

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.

Upvotes: 2

Related Questions