Deleting Rows in VBA

[markjarvisltd]

I have the current code but it keeos failing at the delete row line
Can anyone explain why?

Private Sub CommandButton1_Click()

Dim iRow As Integer
Dim iRowsProcessed As Integer
Dim bAnotherBlank As Boolean

iRowsProcessed = 0

Application.ScreenUpdating = False
Application.Calculation = xlManual

For iRow = 1 To 200
bAnotherBlank = True

If Cells(iRow, 1).Value = "" Then
iRow.EntireRow.Delete

' Have we just moved another blank up to replace
' the one we deleted?
While (bAnotherBlank = True) And (iRowsProcessed < 200)
If Cells(iRow, 1).Value = "" Then
iRow.EntireRow.Delete
Else
bAnotherBlank = False
End If
iRowsProcessed = iRowsProcessed + 1
Wend
Else
iRowsProcessed = iRowsProcessed + 1
End If
Next iRow

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

End Sub

 

[Norie]

mark

Failing it what way?

When deleting you should start from the bottom and work up.

For iRow = 200 To 1 Step -1

 

[markjarvisltd]

Sorry
Compile error:
invalid qualifier
With "iRow" highlighted after the then statement

 

[VoG]

Try

If Cells(iRow, 1).Value = "" Then
Cells(iRow, 1).EntireRow.Delete

and you should loop backwards as Norie said.

 

[markjarvisltd]

Ive corrected as advised now nothing happens

Private Sub CommandButton1_Click()

Dim iRow As Integer
Dim iRowsProcessed As Integer
Dim bAnotherBlank As Boolean

iRowsProcessed = 0

Application.ScreenUpdating = False
Application.Calculation = xlManual

For iRow = 200 To 1 Step -1
bAnotherBlank = True

If Cells(iRow, 1).Value = "" Then
Cells(iRow, 1).EntireRow.Delete

' Have we just moved another blank up to replace
' the one we deleted?
While (bAnotherBlank = True) And (iRowsProcessed < 200)
If Cells(iRow, 1).Value = "" Then
Cells(iRow, 1).EntireRow.Delete
Else
bAnotherBlank = False
End If
iRowsProcessed = iRowsProcessed + 1
Wend
Else
iRowsProcessed = iRowsProcessed + 1
End If
Next iRow

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

End Sub

 

[VoG]

Try this

Private Sub CommandButton1_Click()

Dim iRow As Integer
Application.ScreenUpdating = False
Application.Calculation = xlManual

For iRow = 200 To 1 Step -1
    If Cells(iRow, 1).Value = "" Then Cells(iRow, 1).EntireRow.Delete
Next iRow

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

End Sub

 

[markjarvisltd]

Thanks thats neat
Ive discovered a problem. In posting in from a web page the apparently blank cells are not. If I clear cell your code works.
Is there an way within the code to clear these apparently not so blank cells?

 

[VoG]

Try

Private Sub CommandButton1_Click()

Dim iRow As Integer
Application.ScreenUpdating = False
Application.Calculation = xlManual

For iRow = 200 To 1 Step -1
    If WorksheetFunction.Clean(Cells(iRow, 1).Value) = "" Then Cells(iRow, 1).EntireRow.Delete
Next iRow

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

End Sub

 

[markjarvisltd]

Had to change clean to trim and everthing worked fine
Once again thanks for your invaluable help