Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hpw do I delete multiple empty rows found between filled rows? | Excel Worksheet Functions | |||
How to Delete empty rows in excel in b/w rows with values | Excel Worksheet Functions | |||
Cut filtered rows, paste into next empty row of new sheet, and delete cut rows | Excel Worksheet Functions | |||
How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions | |||
delete empty rows between rows with text | Excel Discussion (Misc queries) |