VBA Macro In Excel To Delete Row If A Certain Cell Is Blank

KlansMan · #1 (**permalink**) 08-27-04, 10:15

Hi There Can Any One Help

I Have This VBA Macro Which Removes Duplicate Rows Can Any One Help Me Modify It To Remove Rows If A Certain Cell Is Blank

Sub Duplicates ()
Current = ""
Do Until ActiveCell = ""
Cnew = ActiveCell.Value
If Cnew = Current Then
Selection.EntireRow.Delete
Else
Current = Activecell.Value
ActiveCell.Offset(1,0).Activate
End If
Loop
End Sub

Hope Some Out There Can Help Me

Kind Regards

KlansMan

DavidCoutts · #2 (**permalink**) 08-27-04, 10:51

this should do it for you

Code:

Sub deleteBlank()
    Dim Lastrow As Integer
    
    Lastrow = Range("A" & Rows.Count).End(xlUp).Row
    
    Range("B2:B" & Lastrow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

this will delete rows where the value in column B is blank
I have assumed that column A is filled
HTH
Dave

shades · #3 (**permalink**) 08-27-04, 10:57

Here is a short way to do it, allowing you to select the column to base the deletion upon. It will delete all rows within the selected column. I use this many times a day.

Code:

Sub DeleteEmptyRowsMain()
' allows user to choose the column by selecting it.
    Dim myColm As Range
    Set myColm = Application.InputBox("Choose column(s) to clear", Type:=8)
    On Error Resume Next
    myColm.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

But note, if you have a few blank rows at the top of your data, it will delete those if the selected column includes blanks in that section. One possible work around is to put dummy data in the cells of the column you want to base the delete upon, then delete the dummy data afterward.

DavidCoutts · #4 (**permalink**) 08-27-04, 11:13

Hi Shades

Ive slightly reworked your code so that you can pick just the top cell of the range of data you want to delete, see if you like it

Code:

Sub DeleteEmptyRowsMain()
' allows user to choose the column by selecting it.
    Dim myColm As Range
    Dim LastCell As Range
    Set myColm = Application.InputBox("Choose Start of column to clear", Type:=8)
    Set LastCell = Cells.Find("*", , , , , xlPrevious)
    On Error Resume Next
    If Not myColm Is Nothing Then
        Range(myColm, Cells(LastCell.Row, myColm.Column)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Else
        MsgBox "No Range Selected", vbOKOnly, "Error"
    End If
        
End Sub

this would get rid of the delete the empty rows up top
just an idea
Dave

shades · #5 (**permalink**) 08-27-04, 12:00

Thanks, David. Works fine. 99% of my work is to get rid of blank cells at the top too, so my version has been sufficient. But I can see where this will be helpful.

BTW, you were faster on the keyboard - must be my age.

Internet Services

Web Development

Digital Marketing

Consumer Tech