+ Reply to Thread
Results 1 to 7 of 7

Delete blank rows

Hybrid View

  1. #1
    Registered User
    Join Date
    10-13-2009
    Location
    Great Falls, Montana
    MS-Off Ver
    Excel 2007
    Posts
    32

    Delete blank rows

    I'm new to vba, and appreciate any help.
    I'm trying to clean up several long worksheets by removing rows that don't contain a visable number or letter. I want to remove all rows that only contain either empty cells, formulas that evaluate to a empty text string (""), cells that contain error values, or cells that contain zeros, or any combination of the previous. I've stumbled onto the following, but it does not work.
    Am I on the right track? Can anyone help modify this or offer a better solution? Thanks in advance.
    Sub DeleteBlankRows()
    Dim LR As Long, i As Long, j As Long
    Dim LC As Integer, ConditionCounter As Integer
    
    Application.ScreenUpdating = False
    
    LR = ActiveSheet.UsedRange.Rows.Count
    LC = ActiveSheet.UsedRange.Columns.Count
    
        For i = 1 To 1 Step -1
            For j = LC To 1 Step -1
                If IsEmpty(Cells(i, j)) = True Then
                ConditionCounter = ConditionCounter + 1
                Else
                    If WorksheetFunction.IsText(Cells(i, j)) = True And (Cells(i, j)) = """" Then
                    ConditionCounter = ConditionCounter + 1
                    Else
                        If WorksheetFunction.IsText(Cells(i, j)) = False And (Cells(i, j)) = 0 Then
                        ConditionCounter = ConditionCounter + 1
                        End If
                    End If
                End If
            Next j
                        If ConditionCounter = LC Then
                        Rows(i).EntireRow.Delete
                        Else
                        End If
                        
        Next i
        
    Application.ScreenUpdating = True
    
    End Sub
    Last edited by Frankf; 10-13-2009 at 03:22 PM.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Help deleting blank rows

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

    I will add them this time
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Help deleting blank rows

    This will remove all entirely blank rows
    Sub DeleteBlanks()
        Dim LR     As Long
        Dim LC     As Long
        Dim j      As Long
        'Application.ScreenUpdating = False
    
        LR = ActiveSheet.UsedRange.Rows.Count
        LC = ActiveSheet.UsedRange.Columns.Count
        For j = LR To 1 Step -1
            If Application.WorksheetFunction.CountA(Range(Cells(j, 1), Cells(j, LC))) = 0 _
               Then Rows(j).EntireRow.Delete
        Next j
    
    End Sub

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Help deleting blank rows

    This should remove all rows with formulas resulting in zeros as well
    Sub DeleteBlanks()
        Dim rng    As Range
        Dim LR     As Long
        Dim LC     As Long
        Dim j      As Long
    
        LR = ActiveSheet.UsedRange.Rows.Count
        LC = ActiveSheet.UsedRange.Columns.Count
        For j = LR To 1 Step -1
            Set rng = Range(Cells(j, 1), Cells(j, LC))
    
            If Application.WorksheetFunction.CountA(Range(Cells(j, 1), Cells(j, LC))) = 0 Then
                Rows(j).EntireRow.Delete
            ElseIf Application.WorksheetFunction.CountIf(rng, 0) = LC _
                   Then Rows(j).EntireRow.Delete
            End If
        Next j
    
    End Sub

  5. #5
    Registered User
    Join Date
    10-13-2009
    Location
    Great Falls, Montana
    MS-Off Ver
    Excel 2007
    Posts
    32

    Re: Delete blank rows

    Thanks for the speedy reply.

    Your macro does remove rows containing only blank cells, but does not remove rows with cells that contain a empty text string (""), or cells with a zero (0). I would like to remove those rows as well. Many rows of my worksheet are populated with cells that evaluate to either a "" or 0 if certian conditions are not met. I don't need those rows unless the formulas in the cells evaluate to either a positive number or visable text.

    Thanks again for your help.

    Frank

  6. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Delete blank rows

    The second code should remove rows where all cells containing formulas return 0, does this work
    Sub DeleteBlanks()
        Dim rng    As Range
        Dim LR     As Long
        Dim LC     As Long
        Dim j      As Long
    
        LR = ActiveSheet.UsedRange.Rows.Count
        LC = ActiveSheet.UsedRange.Columns.Count
        For j = LR To 1 Step -1
            Set rng = Range(Cells(j, 1), Cells(j, LC))
    
            If Application.WorksheetFunction.CountA(Range(Cells(j, 1), Cells(j, LC))) = 0 Then
                Rows(j).EntireRow.Delete
            ElseIf Application.WorksheetFunction.CountIf(rng, 0) = LC _
                   Then Rows(j).EntireRow.Delete
            ElseIf Application.WorksheetFunction.CountIf(rng, """") = LC Then
                Rows(j).EntireRow.Delete
            End If
        Next j
    
    End Sub

  7. #7
    Registered User
    Join Date
    10-13-2009
    Location
    Great Falls, Montana
    MS-Off Ver
    Excel 2007
    Posts
    32

    Thumbs up Re: Delete blank rows

    Thanks again.
    Since some of the rows might contain blank cells, zeros, and/or blank text strings, I just modified your code to add all the conditions up and check to see if that added up to the number of columns in the range. It works perfectly. Thanks for the help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1