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. Le Pingou
    Hello,
    In this case, the problem lies elsewhere; on which line is the procedure code stopped?

    --
    Regards.
    The Penguin
    0
    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
        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
        2. Charles-Daniel


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

          Good evening
          1
          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. 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. 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