Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro does not delete all the required rows
Hi Everyone,
I have a datasheet (a daily report that I get from a system). In this report I have column AE that contains the identifier for that row. Some of the identifiers are DUMMY. These are wrong postings from the system. I have written a following code to delete all rows that have DUMMY in column AE. Sub delete_dummy() Dim Cell As Range For each Cell in Range("AE:AE") If Cell.Value = "DUMMY" Then Cell.EntireRow.Delete End If Next Cell End Sub The code works but it does not delete all the rows containing dummy. I have to run it a few times before all the dummy rows are deleted. I have no idea why that happens and how to fix it so that all the dummy items are deleted in one go. Can someone help me with this. Thanks very much. Regards, -AG |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro does not delete all the required rows
You need to start at the bottom and work to the top
when deleting multiple row. It automatically shifts up after deleting the row so that you skip rows if you go top to bottom. Try this. Sub delete_dummy() Dim i As Range, lstRow As Long LstRow = Cells(Rows.Count, 31).End(xlUp).Row sRng = Range("AE1:AE" & lstRow) For i = lstRow To 1 Step *1 If i.Value = "DUMMY" Then i.EntireRow.Delete End If Next End Sub "AG" wrote: Hi Everyone, I have a datasheet (a daily report that I get from a system). In this report I have column AE that contains the identifier for that row. Some of the identifiers are DUMMY. These are wrong postings from the system. I have written a following code to delete all rows that have DUMMY in column AE. Sub delete_dummy() Dim Cell As Range For each Cell in Range("AE:AE") If Cell.Value = "DUMMY" Then Cell.EntireRow.Delete End If Next Cell End Sub The code works but it does not delete all the rows containing dummy. I have to run it a few times before all the dummy rows are deleted. I have no idea why that happens and how to fix it so that all the dummy items are deleted in one go. Can someone help me with this. Thanks very much. Regards, -AG |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro does not delete all the required rows
I know better than that. Use this:
Sub delete_dummy() Dim i As Range, lstRow As Long LstRow = Cells(Rows.Count, 31).End(xlUp).Row For i = lstRow To 1 Step -1 IfCells( i, 31).Value = "DUMMY" Then Cells(i, 31).EntireRow.Delete End If Next End Sub "AG" wrote: Hi Everyone, I have a datasheet (a daily report that I get from a system). In this report I have column AE that contains the identifier for that row. Some of the identifiers are DUMMY. These are wrong postings from the system. I have written a following code to delete all rows that have DUMMY in column AE. Sub delete_dummy() Dim Cell As Range For each Cell in Range("AE:AE") If Cell.Value = "DUMMY" Then Cell.EntireRow.Delete End If Next Cell End Sub The code works but it does not delete all the rows containing dummy. I have to run it a few times before all the dummy rows are deleted. I have no idea why that happens and how to fix it so that all the dummy items are deleted in one go. Can someone help me with this. Thanks very much. Regards, -AG |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro does not delete all the required rows
Hi JLGWhiz,
Thanks very much for the help. It works fine. I think you meant i As Integer and not Range. I will be interested to know why it does not work when you go from top to bottom. I am sure you would know. Thanks again for your help. Regards, -AG |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to delete rows | Excel Discussion (Misc queries) | |||
Can't get Rows to delete in macro | Excel Programming | |||
Macro to delete rows | Excel Programming | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming |