View original (French)

VBA test for empty line and deletion

Solved
Mrfrize Posted messages 197 Registration date   Status Member Last intervention   -  
 camanagro -
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 Posted messages 12276 Registration date   Status Contributor Last intervention   1 478
     
    Hello,
    In passing, replace [Empty] with [0].
    --
    Regards.
    The Penguin
    2
    1. Mrfrize Posted messages 197 Registration date   Status Member Last intervention   6
       
      Why? It doesn't change anything about my problem.
      0
  2. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    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
  3. Mrfrize Posted messages 197 Registration date   Status Member Last intervention   6
     

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

    What is underlined is what is highlighted in debugging mode.
    1
  4. Charles-Daniel Posted messages 12 Status Member 1
     


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

    Good evening
    1
  6. Le Pingou Posted messages 12276 Registration date   Status Contributor Last intervention   1 478
     
    Hello,
    In this case, the problem lies elsewhere; on which line is the procedure code stopped?

    --
    Regards.
    The Penguin
    0
  7. Charles-Daniel Posted messages 12 Status Member 1
     
    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
  8. Mrfrize Posted messages 197 Registration date   Status Member Last intervention   6
     
    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
  9. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    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 Posted messages 197 Registration date   Status Member Last intervention   6
       
      Thank you for the tip.
      0
  10. 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