Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for deleting rows?
I am working with a large data sample that is in a horrible format. It
includes a lot of rows of data that I do not need, and is in a format that no simple sorting or filtering will not work to give me just the data I want. Can anyone help me with some code to delete all rows that do not have "Agt" or "Agent" in column A? If this is possible I would look for this to happen in rows 6 through 5000. Thank you in advance for your help! Ken |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for deleting rows?
Try this one
More info on this page http://www.rondebruin.nl/delete.htm Sub Loop_Example() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long Dim ArrNames As Variant With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With 'We use the ActiveSheet but you can replace this with 'Sheets("MySheet")if you want With ActiveSheet 'We select the sheet so we can change the window view .Select 'If you are in Page Break Preview Or Page Layout view go 'back to normal view, we do this for speed ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView 'Turn off Page Breaks, we do this for speed .DisplayPageBreaks = False 'Set the first and last row to loop through Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row 'array with names that we want to keep ArrNames = Array("Agt", "Agent") 'We loop from Lastrow to Firstrow (bottom to top) For Lrow = Lastrow To Firstrow Step -1 'We check the values in the A column in this example With .Cells(Lrow, "A") If Not IsError(.Value) Then If IsError(Application.Match(.Value, ArrNames, 0)) Then .EntireRow.Delete End If End With Next Lrow End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "fpd833" wrote in message ... I am working with a large data sample that is in a horrible format. It includes a lot of rows of data that I do not need, and is in a format that no simple sorting or filtering will not work to give me just the data I want. Can anyone help me with some code to delete all rows that do not have "Agt" or "Agent" in column A? If this is possible I would look for this to happen in rows 6 through 5000. Thank you in advance for your help! Ken |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for deleting rows?
Excellent! Thank you Ron!
Can you help me with one last thing? In the line below: ArrNames = Array("Agt", "Agent") Is it possible to add a wildcard to "Agt"? Those are just the first 3 letters of the lines I'm looking for and are followed by more text. None of the rows are unique and are being deleted as a result of the routine. Thank you for your help, you're a godsend! Ken "Ron de Bruin" wrote: Try this one More info on this page http://www.rondebruin.nl/delete.htm Sub Loop_Example() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long Dim ArrNames As Variant With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With 'We use the ActiveSheet but you can replace this with 'Sheets("MySheet")if you want With ActiveSheet 'We select the sheet so we can change the window view .Select 'If you are in Page Break Preview Or Page Layout view go 'back to normal view, we do this for speed ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView 'Turn off Page Breaks, we do this for speed .DisplayPageBreaks = False 'Set the first and last row to loop through Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row 'array with names that we want to keep ArrNames = Array("Agt", "Agent") 'We loop from Lastrow to Firstrow (bottom to top) For Lrow = Lastrow To Firstrow Step -1 'We check the values in the A column in this example With .Cells(Lrow, "A") If Not IsError(.Value) Then If IsError(Application.Match(.Value, ArrNames, 0)) Then .EntireRow.Delete End If End With Next Lrow End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "fpd833" wrote in message ... I am working with a large data sample that is in a horrible format. It includes a lot of rows of data that I do not need, and is in a format that no simple sorting or filtering will not work to give me just the data I want. Can anyone help me with some code to delete all rows that do not have "Agt" or "Agent" in column A? If this is possible I would look for this to happen in rows 6 through 5000. Thank you in advance for your help! Ken |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for deleting rows?
Sorry for the late reply
Sub Loop_Example() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With 'We use the ActiveSheet but you can replace this with 'Sheets("MySheet")if you want With ActiveSheet 'We select the sheet so we can change the window view .Select 'If you are in Page Break Preview Or Page Layout view go 'back to normal view, we do this for speed ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView 'Turn off Page Breaks, we do this for speed .DisplayPageBreaks = False 'Set the first and last row to loop through Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row 'We loop from Lastrow to Firstrow (bottom to top) For Lrow = Lastrow To Firstrow Step -1 'We check the values in the A column in this example With .Cells(Lrow, "A") If Not IsError(.Value) Then If .Value Like "Agt*" Or .Value Like "Agent*" Then 'do nothing Else .EntireRow.Delete End If End If End With Next Lrow End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "fpd833" wrote in message ... Excellent! Thank you Ron! Can you help me with one last thing? In the line below: ArrNames = Array("Agt", "Agent") Is it possible to add a wildcard to "Agt"? Those are just the first 3 letters of the lines I'm looking for and are followed by more text. None of the rows are unique and are being deleted as a result of the routine. Thank you for your help, you're a godsend! Ken "Ron de Bruin" wrote: Try this one More info on this page http://www.rondebruin.nl/delete.htm Sub Loop_Example() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long Dim ArrNames As Variant With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With 'We use the ActiveSheet but you can replace this with 'Sheets("MySheet")if you want With ActiveSheet 'We select the sheet so we can change the window view .Select 'If you are in Page Break Preview Or Page Layout view go 'back to normal view, we do this for speed ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView 'Turn off Page Breaks, we do this for speed .DisplayPageBreaks = False 'Set the first and last row to loop through Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row 'array with names that we want to keep ArrNames = Array("Agt", "Agent") 'We loop from Lastrow to Firstrow (bottom to top) For Lrow = Lastrow To Firstrow Step -1 'We check the values in the A column in this example With .Cells(Lrow, "A") If Not IsError(.Value) Then If IsError(Application.Match(.Value, ArrNames, 0)) Then .EntireRow.Delete End If End With Next Lrow End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "fpd833" wrote in message ... I am working with a large data sample that is in a horrible format. It includes a lot of rows of data that I do not need, and is in a format that no simple sorting or filtering will not work to give me just the data I want. Can anyone help me with some code to delete all rows that do not have "Agt" or "Agent" in column A? If this is possible I would look for this to happen in rows 6 through 5000. Thank you in advance for your help! Ken |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for deleting rows?
Perfect! You're a life saver! Thank you Ron!!
"Ron de Bruin" wrote: Sorry for the late reply Sub Loop_Example() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With 'We use the ActiveSheet but you can replace this with 'Sheets("MySheet")if you want With ActiveSheet 'We select the sheet so we can change the window view .Select 'If you are in Page Break Preview Or Page Layout view go 'back to normal view, we do this for speed ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView 'Turn off Page Breaks, we do this for speed .DisplayPageBreaks = False 'Set the first and last row to loop through Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row 'We loop from Lastrow to Firstrow (bottom to top) For Lrow = Lastrow To Firstrow Step -1 'We check the values in the A column in this example With .Cells(Lrow, "A") If Not IsError(.Value) Then If .Value Like "Agt*" Or .Value Like "Agent*" Then 'do nothing Else .EntireRow.Delete End If End If End With Next Lrow End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "fpd833" wrote in message ... Excellent! Thank you Ron! Can you help me with one last thing? In the line below: ArrNames = Array("Agt", "Agent") Is it possible to add a wildcard to "Agt"? Those are just the first 3 letters of the lines I'm looking for and are followed by more text. None of the rows are unique and are being deleted as a result of the routine. Thank you for your help, you're a godsend! Ken "Ron de Bruin" wrote: Try this one More info on this page http://www.rondebruin.nl/delete.htm Sub Loop_Example() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long Dim ArrNames As Variant With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With 'We use the ActiveSheet but you can replace this with 'Sheets("MySheet")if you want With ActiveSheet 'We select the sheet so we can change the window view .Select 'If you are in Page Break Preview Or Page Layout view go 'back to normal view, we do this for speed ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView 'Turn off Page Breaks, we do this for speed .DisplayPageBreaks = False 'Set the first and last row to loop through Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row 'array with names that we want to keep ArrNames = Array("Agt", "Agent") 'We loop from Lastrow to Firstrow (bottom to top) For Lrow = Lastrow To Firstrow Step -1 'We check the values in the A column in this example With .Cells(Lrow, "A") If Not IsError(.Value) Then If IsError(Application.Match(.Value, ArrNames, 0)) Then .EntireRow.Delete End If End With Next Lrow End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "fpd833" wrote in message ... I am working with a large data sample that is in a horrible format. It includes a lot of rows of data that I do not need, and is in a format that no simple sorting or filtering will not work to give me just the data I want. Can anyone help me with some code to delete all rows that do not have "Agt" or "Agent" in column A? If this is possible I would look for this to happen in rows 6 through 5000. Thank you in advance for your help! Ken |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for deleting rows?
On 20 Sep, 17:38, fpd833 wrote:
Excellent! Thank you Ron! Can you help me with one last thing? In the line below: ArrNames = Array("Agt", "Agent") Is it possible to add a wildcard to "Agt"? Those are just the first 3 letters of the lines I'm looking for and are followed by more text. None of the rows are unique and are being deleted as a result of the routine. Thank you for your help, you're a godsend! Ken "Ron de Bruin" wrote: Try this one More info on this page http://www.rondebruin.nl/delete.htm Sub Loop_Example() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long Dim ArrNames As Variant With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With 'We use the ActiveSheet but you can replace this with 'Sheets("MySheet")if you want With ActiveSheet 'We select the sheet so we can change the window view .Select 'If you are in Page Break Preview Or Page Layout view go 'back to normal view, we do this for speed ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView 'Turn off Page Breaks, we do this for speed .DisplayPageBreaks = False 'Set the first and last row to loop through Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row 'array with names that we want to keep ArrNames = Array("Agt", "Agent") 'We loop from Lastrow to Firstrow (bottom to top) For Lrow = Lastrow To Firstrow Step -1 'We check the values in the A column in this example With .Cells(Lrow, "A") If Not IsError(.Value) Then If IsError(Application.Match(.Value, ArrNames, 0)) Then .EntireRow.Delete End If End With Next Lrow End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "fpd833" wrote in ... I am working with a large data sample that is in a horrible format. It includes a lot of rows of data that I do not need, and is in a format that no simple sorting or filtering will not work to give me just the data I want. Can anyone help me with some code to delete all rows that do not have "Agt" or "Agent" in column A? If this is possible I would look for this to happen in rows 6 through 5000. Thank you in advance for your help! Ken- Hide quoted text - - Show quoted text - From Phillip London UK I was just browsing the group and saw your message You may be interested in this solution as it is very fast due to no physical looping of rows. Also it uses wildcards as you requested and the column A data can be sorted or unsorted You can modify the range if you want to be A7:A65535 Sub tester() Dim Crow As Long Dim ArrNames As Variant Dim Ele As Variant ArrNames = Array("HR*", "AC*") For Each Ele In ArrNames Do While Not IsError(Application.Match(Ele, Range("A:A"), 0)) Crow = Application.Match(Ele, Range("A:A"), 0) Rows(Crow).Delete Loop Next End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for deleting rows?
Phillip wrote:
<<You may be interested in this solution as it is very fast due to no physical looping of rows. Not quite true. The code ... Do While Not IsError(Application.Match... Rows(Crow).Delete Loop .... is, in fact, a loop. It selects each matching row, one by one, and deletes them, one at a time. Data Autofiltering techniques may be faster, but take a little more effort to set up. -- Regards, Bill Renaud |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting rows in two columns with a code in visual basic | Excel Discussion (Misc queries) | |||
VBA Code for deleting rows with specific value | Excel Programming | |||
Code deleting all rows in error | Excel Programming | |||
Help with Chip Pearson's Code for Deleting Blank Rows | Excel Programming | |||
VBA code for Deleting rows by verification | Excel Programming |