Delete rows.
 

Hello,

Some assistance please.

The code below is intended to delete the entire row if an “x” is in th
first column. It loops though all the sheets and all the populated row
in each sheet.

The problem appears to be in the way it moves though the rows.
If there are several continual rows with an “x” every other row i
deleted.
Thanks for the help.

Best Regards

Ron


Sub DeleteRecords()

' Delete records with an “x” in the first column
For sht = 1 To Worksheets.Count
Sheets(sht).Select
LastRow = ActiveSheet.UsedRange.Rows.Count

For x = 5 To LastRow 'set x value to first row of data
Cells(x, 1).Select

If LCase(ActiveCell.Value) = "x" Then
Selection.EntireRow.Delete
End If

Next x
Next sht

End Su

--
Message posted from http://www.ExcelForum.com

Delete rows.
 

Work backwards! As the rows is deleted the code has already passed the row that fills it's place
change
For x = 5 To LastRow
to
For x = LastRow to 5 step -

HTH

Delete rows.
 

Hi Ron,

Amending your code to reverse the loop sequence and removing unnecessary
selection of the individual sheets or the cells, Try:

Sub DeleteRecords()
Dim sh As Worksheet
Dim LastRow As Long
Dim x As Long

Application.ScreenUpdating = False
For Each sh In ActiveWorkbook.Worksheets
LastRow = sh.UsedRange.Rows.Count

For x = LastRow To 5 Step -1

With sh.Cells(x, 1)
If LCase(.Value) = "x" Then
.EntireRow.Delete
End If
End With

Next x
Next sh

End Sub

---
Regards,
Norman



"Ron " wrote in message
...
Hello,

Some assistance please.

The code below is intended to delete the entire row if an "x" is in the
first column. It loops though all the sheets and all the populated rows
in each sheet.

The problem appears to be in the way it moves though the rows.
If there are several continual rows with an "x" every other row is
deleted.
Thanks for the help.

Best Regards

Ron


Sub DeleteRecords()

' Delete records with an "x" in the first column
For sht = 1 To Worksheets.Count
Sheets(sht).Select
LastRow = ActiveSheet.UsedRange.Rows.Count

For x = 5 To LastRow 'set x value to first row of data
Cells(x, 1).Select

If LCase(ActiveCell.Value) = "x" Then
Selection.EntireRow.Delete
End If

Next x
Next sht

End Sub


---
Message posted from http://www.ExcelForum.com/

Delete rows.
 

Always work bottom up when deleting rows, or build a range of rows to be
deleted as you go, and delete them at the end.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ron " wrote in message
...
Hello,

Some assistance please.

The code below is intended to delete the entire row if an "x" is in the
first column. It loops though all the sheets and all the populated rows
in each sheet.

The problem appears to be in the way it moves though the rows.
If there are several continual rows with an "x" every other row is
deleted.
Thanks for the help.

Best Regards

Ron


Sub DeleteRecords()

' Delete records with an "x" in the first column
For sht = 1 To Worksheets.Count
Sheets(sht).Select
LastRow = ActiveSheet.UsedRange.Rows.Count

For x = 5 To LastRow 'set x value to first row of data
Cells(x, 1).Select

If LCase(ActiveCell.Value) = "x" Then
Selection.EntireRow.Delete
End If

Next x
Next sht

End Sub


---
Message posted from http://www.ExcelForum.com/