[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 752
  • Last Modified:

macro to delete blank empty row set data range

Hi Expert's excel 2007

(starting point a2) and data range a2:g15000 to across columns and rows a2: and delete th3 entire row where the cell are either empty or blank.
0
route217
Asked:
route217
  • 2
1 Solution
 
MacroShadowCommented:
I'm sorry your question isn't clear please rephrase it.

Do you want to delete the rows only if all cells in the row are blank or also if only one of them is blank?
0
 
MacroShadowCommented:
Sub DeleteBlankRows1()

'Deletes the entire row within the selection if the ENTIRE row contains no data.

    Dim rng As Range
    Dim i As Long

    Set rng = Range("A2:G15000")

    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False

        For i = rng.Rows.Count To 1 Step -1
            If WorksheetFunction.CountA(rng.Rows(i)) = 0 Then
                rng.Rows(i).EntireRow.Delete
            End If
        Next i

        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With

End Sub

Sub DeleteBlankRows2()

    'Deletes the entire row within the range if SOME of the cells within the range contain no data

    Dim rng As Range
    
    Set rng = Range("A2:G15000")
    On Error Resume Next
    rng.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
    On Error GoTo 0

End Sub

Open in new window

0
 
route217Author Commented:
Thanks for the feedback and apologies for the late reply. ..internet problems.

Let me test.
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now