How to delete these empty rows-CodePudding

I want to delete empty rows, such as 3-10 and 16-19. I tried the following code but it is also deleting rows 1, 2 and 15.

Dim s1 as worksheet
Dim LastRow As Long

Set s1 = ThisWorkbook.Worksheets(9)

LastRow = s1.Range("D" & s1.Rows.Count).End(xlUp).Row
With s1.Range("D2:D" & LastRow)
    If WorksheetFunction.CountBlank(.Cells) > 0 Then
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End If
End With

CodePudding user response：

Looking at @cybernetic.nomads comment, and the fact that I just had a quick go, there will be a better way to do this.

If you want to delete all the empty rows, you'll (probably) need a loop.

When you delete a row your code needs to take in to account that the row numbers change (delete row 1, and now row 2 is actually row 1). You can do this by looping backward, or by re-trying the current row until all empty rows are gone then increase the row number (like my example)

Sub main()
Dim Sheet As Worksheet: Set Sheet = ThisWorkbook.Worksheets("Sheet1")
Dim Row As Integer: Row = 2 ' start row
Do
    If Row > Sheet.Range("D" & Sheet.Rows.Count).End(xlUp).Row Then ' break out once you've gotten to the end
        Exit Do
    End If
    
    If Application.WorksheetFunction.CountA(Sheet.Rows(Row)) = 0 Then ' check if the row is empty
        Sheet.Rows(Row).Delete xlShiftUp ' delete the row and shift up
    Else
        Row = Row   1 ' row was not empty, move on to the next row
    End If
Loop
End Sub

CodePudding user response：

your code check only column D and deletes blanks there. text in lines 1, 2 and 15 are in columns A and B.

perhaps you could use this twist in your code?

while removed, instead i is used to check single rows
Rows are checked from bottom up
if a row has 4 blanks in columns A:D, this row is deleted

Code

Dim s1 As Worksheet
Dim LastRow As Long
Dim i As Long

Set s1 = ThisWorkbook.Worksheets(9)

LastRow = s1.Range("D" & s1.Rows.Count).End(xlUp).Row
If WorksheetFunction.CountBlank(s1.Range("A2:D" & LastRow).Cells) > 0 Then
    For i = 1 To LastRow - 2: '-2 means: row 2 will be checked last. Use '-1' to check row 1 as well
        If WorksheetFunction.CountBlank(Range(Cells(LastRow - i, 1), Cells(LastRow - i, 4))) = 4 Then
            Rows(LastRow - i).Delete
        End If
    Next i
End If

Note: if you are not dealing with many rows, you might consider using integer Instead of Long

CodePudding user response：

A simple solution is to check last column to garantee entire empty row

lastline = s1.Cells(Rows.Count, 4).End(xlUp).Row
i = lastline
While (i > 0)
    If (s1.Cells(i, Columns.Count).End(xlToLeft).Column = 1 And s1.Cells(i, 1).Value = "") Then
        s1.Rows(i).EntireRow.Delete
    End If
    i = i - 1
Wend

Note that the loop is backwards because (i) delete a row reindex all of the others next and (ii) classical for condition (for i = 1 to lastline) is checked just once.