Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    50

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

    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

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    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

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    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.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    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

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    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.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •