VBA Error on Logic to Remove Rows based on Blank Cells

[jay_hl]

Hello

I have some VBA which removes a row, based on the cells in a selected column being blank. Its quite powerful, so wanted to add a check that I don’t select an entirely blank column, where all the rows would be deleted (ie the whole sheet).

I therefore wanted to count how many rows it was expecting to remove, and if that totaled all rows in the Sheet, give me a Yes/No warning box. However I'm getting an error.

Can anyone tweak my code to work, including for an entirely blank column.

Thanks. Jay

----
Sub Macro1()

SelectedColumn = ActiveCell.Column
If Columns(SelectedColumn).SpecialCells(xlCellTypeBlanks).CountA = 1048576 Then

MsgBox "You are about to remove all rows in the sheet." & vbCrLf & vbCrLf & "Are you sure you have selected the correct column?", vbYesNo, "Are you sure?"

If vbYes Then

Columns(SelectedColumn).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Else
Exit Sub
End If

Else

Columns(SelectedColumn).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End If

End Sub

 

[awoohaw]

When you step through the code what line causes the error?

 

[jay_hl]

This one
Columns(SelectedColumn).SpecialCells(xlCellTypeBlanks).CountA

Ive also tried, but no luck
Columns(SelectedColumn).SpecialCells(xlCellTypeBlanks).Cells.CountA

 

[jay_hl]

Hello, is anyone able to help tweak the VBA I posted, as I still haven’t managed to crack it yet. Thanks.

 

[Alex Blakenburg]

1) CountA doesn't exist as VBA property.
2) The speciall cells function seems to be aware of the used range and as such will never return 1048576 unless you have something entered on that row which seems unlikely.
It only counts blanks down to the last row with something in it or has been formatted
The code below checks if the number of blank cells in the column = the number of rows from row 1 to the last used row, which would mean there is no data in that column.

Sub DeleteBlankCellsInColumn()
    Dim SelectedColumn As Long
    SelectedColumn = ActiveCell.Column
    ActiveSheet.UsedRange                       ' Ensure used range is up to date
    With Columns(SelectedColumn)
        ' Check if blank cells in column = all rows in sheet
        If .SpecialCells(xlCellTypeBlanks).Count = .SpecialCells(xlCellTypeLastCell).Row Then
            MsgBox "You are about to remove all rows in the sheet." & vbCrLf & vbCrLf & "Are you sure you have selected the correct column?", vbYesNo, "Are you sure?"
            If vbYes Then
                .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
            Else
                Exit Sub
            End If
        Else
             .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        End If
    End With
End Sub