VBA test for empty line and deletion
Solved
Mrfrize
Posted messages
197
Registration date
Status
Membre
Last intervention
-
camanagro -
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
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 réponses
Mrfrize
Posted messages
197
Registration date
Status
Membre
Last intervention
6
Why? It doesn't change anything about my problem.
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.
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.
If Application.CountA(Rows(r)) = Empty Then Rows(r).EntireRow.Delete
What is underlined is what is highlighted in debugging mode.
Hello,
In this case, the problem lies elsewhere; on which line is the procedure code stopped?
--
Regards.
The Penguin
In this case, the problem lies elsewhere; on which line is the procedure code stopped?
--
Regards.
The Penguin
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
For r = lastline To fistline Step -1
il faut
For r = lastline To firstline Step -1
et ça tourne.
Salutations
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.
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.
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!!
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!!