0

I try to get rid of blank rows.

I have data in columns A:H and need to write a code which will delete entire row only if ALL cells in specific row in columns A:H are blank, like A2:H2 are all blank then delete entire row.

If there is even one cell with some value in this row in columns A:H, then leave it as is.

I wrote below loop, but it is super slow and seems not to be working. Is there any other way to achieve the above?

PS. 7500 will be replaced by code to check last row index.

Dim sh3 As Worksheet
Set sh3 = Workbooks(file_name).Sheets("TransformedData")

Dim i, j As Long


'row counter
For i = 2 To 7500 Step -1

    'column counter
    For j = 1 To 8
        If sh3.Cells(i, j).Value = "" Then
            sh3.Rows(i).EntireRow.Delete
        End If
    Next j
    
Next i

EDIT/UPDATE: Alright I found the solution which is working. Thanks for advice on my incorrect for statement, it should be 7500 to 2 Step -1 indeed.

Below code is giving me correct output. But any advice on optimization would be appreciated :)

Dim i, j As Long

'row counter For i = 7500 To 2 Step -1

'reset column index
j = 1

'column counter
    If sh3.Cells(i, j).Value = "" Then
        If sh3.Cells(i, j + 1).Value = "" Then
            If sh3.Cells(i, j + 2).Value = "" Then
                If sh3.Cells(i, j + 3).Value = "" Then
                    If sh3.Cells(i, j + 4).Value = "" Then
                        If sh3.Cells(i, j + 5).Value = "" Then
                            If sh3.Cells(i, j + 6).Value = "" Then
                                If sh3.Cells(i, j + 7).Value = "" Then
                        
                                    sh3.Rows(i).EntireRow.Delete
                                    

                                End If

                            End If

                        End If

                    End If

                End If

            End If

        End If

    End If

Next i

k.patrick
  • 1
  • 2
  • Try with `for i = 7500 to 2 Step -1 ...` since you want to delete the rows from down upward. – Tom Brunberg Aug 11 '22 at 13:15
  • When deleting rows, always go backwards: from bottom to top – iDevlop Aug 11 '22 at 13:15
  • 1
    Even better unionize a range to delete and then delete them all at once instead of row by row. – Warcupine Aug 11 '22 at 13:17
  • https://stackoverflow.com/a/9379968 in the linked thread. – BigBen Aug 11 '22 at 13:20
  • Alright, I found the solution: indeed I did a mistake in for loop, it should be "7500 to 2 step -1", thanks for that. – k.patrick Aug 11 '22 at 13:23
  • You really shouldn't use the approach you have (instead see the linked answer from my previous comment), but if you insist on using your approach, use `WorksheetFunction.CountA` instead of 8 `If...End If`. – BigBen Aug 11 '22 at 13:51

0 Answers0