none
Delete whole empty row in a selection RRS feed

  • Question

  • i use below code to delete whole empty row in a selection. When i select a cell only it is not working........Can u pls correct my code  or advise me better code??
    =================================================
    Sub DltRwOfBlnkClls()
    Dim SelctnRng As Range
    Dim i As Long
    Dim xRW As Integer
    On Error Resume Next
    xTitleId = "Delete Rows of Blank Cells"
    Set SelctnRng = Application.Selection
    Set SelctnRng = Application.InputBox("Select Range:", xTitleId, SelctnRng.Address, Type:=8)
    If Err.Number = 424 Then
    Exit Sub
    End If
    On Error GoTo 0

    xRW = SelctnRng.Find(What:="*", After:=SelctnRng.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
    With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    For i = xRW To 1 Step -1
    If WorksheetFunction.CountA(SelctnRng.Rows(i)) = 0 Then
    SelctnRng.Rows(i).EntireRow.Delete
    End If
    Next

    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
    End With
    End Sub

    Monday, March 13, 2017 6:16 PM

All replies

  • Hi Mohashin,

    Thanks for visiting our forum.

    Then this forum mainly focus on general questions and feedback about Excel client. Since your issue is about code, I'll move your thread to the following dedicated MSDN forum for Excel:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Best regards,
    Yuki Sun


    Please remember to mark the replies as answers if they helped.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Tuesday, March 14, 2017 2:05 AM
  • Mohashin -

    Took me a little to figure out the behavior you wanted. It looks like you want a user to select a range of cells, then examine each cell and delete the entire row for empty cells. I took your code and re-wrote a bit:

    Option Explicit
    
    Sub DltRwOfBlnkClls()
        Dim SelctnRng As Range
        Dim xTitleId As String
        Dim lngRow As Long
        Dim xRW As Integer
        Dim rngFirstCell As Excel.Range
        
        On Error Resume Next
        xTitleId = "Delete Rows of Blank Cells"
        Set SelctnRng = Excel.ActiveCell
        Set SelctnRng = Application.InputBox("Select Range:", xTitleId, Excel.ActiveCell.Address, Type:=8)
        If Err.Number = 424 Then
            Exit Sub
        End If
    
        If Not (SelctnRng Is Nothing) Then
            On Error GoTo 0
            xRW = SelctnRng.Rows.Count
            Set rngFirstCell = SelctnRng.Cells(1, 1)
            Excel.Application.Calculation = xlCalculationManual
            Excel.Application.ScreenUpdating = False
            For lngRow = xRW - 1 To 0 Step -1
                If Not IsError(rngFirstCell.Offset(lngRow, 0)) Then     ' The result of a formula might be an error
                    If rngFirstCell.Offset(lngRow, 0).Value = "" Then
                        rngFirstCell.Offset(lngRow, 0).EntireRow.Delete
                    End If
                End If
            Next lngRow
            Excel.Application.Calculation = xlCalculationAutomatic
            Excel.Application.ScreenUpdating = True
        End If
     End Sub
    
    
    

    Hope that get you on track!


    -MainSleuth You've Got It, Use It! Engineering, Science, Statistics Solutions http://ToolSleuth.com. For any reply that either helps to answer your question or is the answer, please mark it as helpful or as the answer so others with the same question will have an answer quickly.

    Tuesday, March 14, 2017 4:32 AM
  • Hi Mohashin,
    >>i use below code to delete whole empty row in a selection                          
    According to your description,I think you want to delete the whole row if all cells in the row are empty,right?
    >> xRW = SelctnRng.Find(What:="*", After:=SelctnRng.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
    I’d like to confirm your purpose of using Find function, so that we could provide suitable solutions for you.
    The code will return the row index of the last cell in the selection.If you want a row count for loop,you could try Rows.Count.
    Please take a reference at the following code.

    Sub DltRwOfBlnkClls()
    Dim SelctnRng As Range
    Dim i As Long
    Dim xRW As Integer
    On Error Resume Next
    xTitleId = "Delete Rows of Blank Cells"
    Set SelctnRng = Application.Selection
    Set SelctnRng = Application.InputBox("Select Range:", xTitleId, SelctnRng.Address, Type:=8)
    If Err.Number = 424 Then
    Exit Sub
    End If
    On Error GoTo 0
    xRW = SelctnRng.Rows.Count
    With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    For i = xRW To 1 Step -1
    If WorksheetFunction.CountA(SelctnRng.Rows(i)) = 0 Then
    SelctnRng.Rows(i).EntireRow.Delete
    End If
    Next
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
    End With
    End Sub

    Best regards,
    Terry

    Tuesday, March 14, 2017 7:16 AM