none
Excel VBA to delete blank rows

    Question

  • Hi

    I have previously recieved help with this matter (thanks Hans) but have run into a problem.

    I have created a command button on a userform "roll" I named the command button "Compatrol" The function of the button is to delete blank rows in Colum D on a sheet called "patrol" then sort the the table firstly range B1 and then range D1. The macro is sorting the table but does not delete the blank rows. 

    Thanks in advance for your time and  help 

    Gerry

    VBA novice

    Private Sub Compatrol_Click()
       
         Dim rng As Range
         On Error GoTo ErrHandler
         Set rng = Worksheets("Patrol").Range("D2:D133").SpecialCells(xlCellTypeBlanks)
         If Not rng Is Nothing Then
             rng.EntireRow.Delete
         End If
         Exit Sub
    ErrHandler:
         ' There were no blank cells, so there is nothing to do
     
       
        Worksheets("Patrol").Range("A2:F133").Sort _
            Key1:=Worksheets("Patrol").Range("B1"), Order1:=xlAscending, _
            Key2:=Worksheets("Patrol").Range("D1"), Order2:=xlAscending, _
            Header:=xlYes
     
          
    End Sub

    Wednesday, February 01, 2012 12:10 PM

Answers

  • I tested the code, and it does delete rows where column D is really blank. It won't delete rows where column D looks blank but isn't, for example if the cell in column D contains one or more spaces, or a formula that returns an empty string "".

    Could that be the cause of the problem?


    Regards, Hans Vogelaar
    Wednesday, February 01, 2012 12:54 PM

All replies

  • I tested the code, and it does delete rows where column D is really blank. It won't delete rows where column D looks blank but isn't, for example if the cell in column D contains one or more spaces, or a formula that returns an empty string "".

    Could that be the cause of the problem?


    Regards, Hans Vogelaar
    Wednesday, February 01, 2012 12:54 PM
  • Hi Hans

    I will be away from PC until this evening I will check the "blank" cell and make sure they contain no data ie spaces.

     

    Thanks for help.

     

    Gerry

    Wednesday, February 01, 2012 1:19 PM
  • Thanks Hans for push in the right direction.

     

    Gerry

    Thursday, February 02, 2012 10:29 AM
  • Hi Hans

     

    How do you adjust the code to work where the sheet contains  table headers.

     

    Gerry

    Thursday, February 02, 2012 11:17 AM
  • I don't think the code would need to be changed. If you do have problems, could you provide more info?
    Regards, Hans Vogelaar
    Thursday, February 02, 2012 4:18 PM
  • Hi Hans,

    i have a worksheet which was imported from a database and hence has extra rows, i have the macro to remove extra rows if the entire row is blank, but some empty rows contains '-' in column AK to AL and hence does not get deleted.  since there are about 200 rows in a worksheet and about 40 worksheets, manual delete is too hard.  please help, it just needs to find blanks from row 'A' to 'Z' then delete entire row so the '-' gets deleted.  there are '-' in good rows too.

    Public Sub RemoveEmptyRows()

       Dim LastRow As Long, CurrentRow As Long

       Dim EntireRow As Range

       Dim i As Integer

          For i = 1 To Worksheets.Count

          Sheets(i).Select

             LastRow = Cells.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

             For CurrentRow = LastRow To 1 Step -1

                Set EntireRow = Cells(CurrentRow, 1).EntireRow

                 If Application.WorksheetFunction.CountA(EntireRow) = 0 Then EntireRow.Delete

            Next CurrentRow

        Next i

    End Sub

    Friday, May 18, 2012 6:56 AM
  • You could change the line

    Set EntireRow = Cells(CurrentRow, 1).EntireRow

    to

    Set EntireRow = Range(Cells(CurrentRow, 1), Cells(CurrentRow, 26))

    This includes only columns A:Z instead of the entire row.

    Regards, Hans Vogelaar

    Friday, May 18, 2012 9:38 AM