Deleting Rows in VBA

markjarvisltd

Board Regular
Joined
Sep 17, 2004
Messages
92
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
mark

Failing it what way?

When deleting you should start from the bottom and work up.
Code:
For iRow = 200 To 1 Step -1
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

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

and you should loop backwards as Norie said.
 

markjarvisltd

Board Regular
Joined
Sep 17, 2004
Messages
92
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

Legend
Joined
Jun 19, 2002
Messages
63,650
Try this

Code:
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

Board Regular
Joined
Sep 17, 2004
Messages
92
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

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
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

Board Regular
Joined
Sep 17, 2004
Messages
92
Had to change clean to trim and everthing worked fine
Once again thanks for your invaluable help
 

Forum statistics

Threads
1,181,658
Messages
5,931,269
Members
436,785
Latest member
KingGideon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top