Excel Simple VBA to Delete Blank Rows

[Randy11]

The Simple code below deletes all blank rows in data contained in the range (A1:A10000)
What I would like to do is delete any rows that have no Data in the row at all in any cell. If there is not an easy solution to this, no data in each row A1:Z10000.
So if A1 to Z1 have no date, delete this line, move to next & apply same.
Ideas appreciate.

Sub Simple_Blank_Row_Delete()

Range("A1:A10000").SpecialCells(xlCellTypeBlanks).EntireRow.Delete


End Sub

 

[chemebabak]

This works:

Sub delete_row_if_one_of_the_cells_in_the_row_is_blank()
    Dim a, i, num_of_rows, num_of_cols, num_of_cells_with_data As Integer
    'number of rows and columns
    num_of_rows = 10
    num_of_cols = 26
    a = 1
    For i = 1 To num_of_rows
            'count the number of cells with data in row a, from column 1 to num_of_cols
            num_of_cells_with_data = WorksheetFunction.CountA(Range(Cells(a, 1), Cells(a, num_of_cols)))
            'With Selection
            If num_of_cells = 0 Then
                Cells(a, 1).EntireRow.Delete
            Else: a = a + 1
            End If
    Next i
End Sub

 

[Gruuuu]

chemebabak, I think you read his logic slightly backwards; he wishes to delete a row if every cell in the row is blank.

This seems like a fairly efficient loop:
[tt]
Sub deleteBlanks()
Dim i As Long
Dim c As Integer
Dim aws As Worksheet

Set aws = ActiveSheet

With aws
c = .UsedRange.Columns.Count
For i = .UsedRange.Rows.Count To 1 Step -1
If .Range(.Cells(i, 1), .Cells(i, c)).SpecialCells(xlCellTypeBlanks).Count = c Then
.Rows(i).EntireRow.Delete
End If
Next i

End With

Set aws = Nothing

End Sub
[/tt]

 

[Randy11]

Performed 2 tests with code to verify it would do what was desired.
1st Test:
1st time I ran the code from a command button to test it removed the blanks rows. I clicked on it a 2nd time & it deleted the last row with data on it. clicked a 3rd time it did nothing?
2nd Test: So I disabled last line of code & ran again with blanks in data again 1st time it deleted blank rows.
3rd test Enabled last line of code again, no gaps in data now , tested & got a VBA Error 1004 No Cells were found.

Seeing how this can work, here are a few additional conditions to consider:
1) Code needs to be able to run without error whether there are blank rows or not. If blank rows delete, if not exit sub

Have modified code to add a simple error handler, this appears to have resolved the issues. Appreciate 2nd set of eyes on this, is there something in the code that may be causing the unusual / inconsistent results? Seems to work OK now...

Sub deleteBlanks()
Dim i As Long
Dim c As Integer
Dim aws As Worksheet

On Error GoTo ErrorHandler

Set aws = ActiveSheet

With aws
c = .UsedRange.Columns.Count
For i = .UsedRange.Rows.Count To 1 Step -1
If .Range(.Cells(i, 1), .Cells(i, c)).SpecialCells(xlCellTypeBlanks).Count = c Then
.Rows(i).EntireRow.Delete
End If
Next i

End With

Set aws = Nothing

ErrorHandler:
Set aws = Nothing
Exit Sub

End Sub

 

[Gruuuu]

Odd, it acted fine for me on multiple attempts. I do recall that there may be some updating issues with using the WorkSheet.UsedRange property.

When you say you disabled the "last line of code"
What did you mean? the "Set aws = Nothing" part?

 

[Randy11]

Yes, just dropped a text marker in front of it '

 

[SkipVought]

There is no good reason the declare and set a sheet object...

Sub deleteBlanks()
    Dim i As Long
    Dim c As Integer
    
    On Error GoTo ErrorHandler
    
    With ActiveSheet
        c = .UsedRange.Columns.Count
        For i = .UsedRange.Rows.Count To 1 Step -1
            If .Range(.Cells(i, 1), .Cells(i, c)).SpecialCells(xlCellTypeBlanks).Count = c Then
                .Rows(i).EntireRow.Delete
            End If
        Next i
    
    End With
    
ErrorHandler:
    
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

I do recall that there may be some updating issues with using the WorkSheet.UsedRange property.

The UsedRange property indicates the extent of the range of DATA on a sheet, not the extent of the range of VALUES on a sheet.

Incorrect results can be experienced if the programmer does not understand and anticipate the difference between DATA and VALUES.

I use UsedRange regularly with confidence, consistency and success, because I 'leave no DATA behind,' which WILL happen if you merely use the DELETE key to clear unused areas.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gruuuu]

Skip, what do you recommend for removing data both manually and with VBA?
Just deleting the range instead of clearing?/Range.Delete instead of Range.ClearContents?

 

[SkipVought]

the Delete or Clear methods in VBA

Select > Right-Click > DELETE shift wherever.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]