View original (French)

VBA test for empty line and deletion

Solved
Mrfrize -  
Hello,

I have browsed the forum quite a bit and I haven't found the answer to my question.

I want to delete the rows that are completely empty from a table. My macro works but shows this error message and I don't understand why.

Run-time error '1004':
Application-defined or object-defined error

Here is the said macro:
sub supligne
Dim lastline,firstline as integer

'finds the last line of the table
lastline = ActiveSheet.UsedRange.Rows.Count

'finds the first line of the data table
For a = 1 To lastline Step 1
If Cells(a, 1).Value = "Priority" Then
firstline = a + 1
End If
Next a

'Deletes the empty rows

For r = lastline To fistline Step -1
If Application.CountA(Rows(r)) = Empty Then Rows(r).EntireRow.Delete

Next r
end sub

I tried changing empty to "" but it works even less well.

Can someone tell me where I am going wrong?

Thank you very much

PS: sorry if I don't put all the accents but I am currently working with a qwerty keyboard.

Configuration: Windows XP / Internet Explorer 7.0

9 answers

  1.  Le Pingou
    Hello,
    In passing, replace [Empty] with [0].
    --
    Regards.
    The Penguin
    2
    1. Mrfrize Author
      Why? It doesn't change anything about my problem.
      0
  2. Mike-31
    Hi everyone,

    I'm arriving late to the party, but here we go

    Option Explicit

    Sub supligne()
    Dim lastline, firstline As Integer
    Dim a, r As Long
    'find the last row of the table
    lastline = ActiveSheet.UsedRange.Rows.Count
    'find the first row of the data table
    For a = 1 To lastline Step 1
    If Cells(a, 1).Value = "Priority" Then
    firstline = a + 1
    End If
    Next a
    For r = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
    If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
    Next r
    End Sub

    or more simply

    Sub Suplignvides()
    Dim r As Long
    For r = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
    If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
    Next r
    End Sub
    --
    See you +
    Mike-31

    A period of failure is a perfect time to sow the seeds of knowledge.
    2
    1. Mrfrize Author

      If Application.CountA(Rows(r)) = Empty Then Rows(r).EntireRow.Delete

      What is underlined is what is highlighted in debugging mode.
      1
      1. Charles-Daniel


        If Application.CountA(Rows(r)) = Empty Then Rows(r).EntireRow.Delete

        Good evening
        1
        2. Le Pingou
          Hello,
          In this case, the problem lies elsewhere; on which line is the procedure code stopped?

          --
          Regards.
          The Penguin
          0
          1. Charles-Daniel
            Mauvaise définition de la variable. Au lieu de
            For r = lastline To fistline Step -1

            il faut
            For r = lastline To firstline Step -1

            et ça tourne.
            Salutations
            0
            1. Mrfrize Author
              Thank you very much,

              And to think I spent several hours looking for why it wasn't working. I will pay more attention next time, it's really stupid to waste so much time on that.

              Thanks again.
              0
              1. eriiic Contributor
                Hello,

                To avoid variable name errors, go to 'options / editor' (from VBE) and check 'Require Variable Declaration'
                All your modules will start with Option Explicit and you won't be able to enter an undeclared name.

                eric
                0
                1. Mrfrize Author
                  Thank you for the tip.
                  0
              2. camanagro
                Hello,

                I would also like to delete the empty rows from my table and I have therefore copied exactly what has been said... However, even though the macro works, I still get this error message... (I made sure to replace fist with first, and even using the latest macro Suplignvides(), I still get this message...

                As a secondary question, ideally I would like to delete all the empty rows from my spreadsheet except those followed by a row whose first cell is not empty. I tried using if "hacky" loops that don't work at all so I suppose there is a better way similar to the one mentioned above.

                Thank you for your help!!
                0