Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code modification?
I was graciously provided a macro that deletes all rows not having certain
values in col A. In the following line: 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. I have pasted the provided code below, should the above make no sense. Thank you for your help!! Ken 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 With ActiveSheet .Select ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView .DisplayPageBreaks = False Firstrow = .UsedRange.Cells(7).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row ArrNames = Array(" Agt ID", "Agent Summary") For Lrow = Lastrow To Firstrow Step -1 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code modification?
I believe you could use the LIKE operator to do this.
If YourString like "ABC*" 'Finds cells that start with ABC. If YourString like "*ABC*" 'Finds cells with ABC anywhere in them -- HTH, Barb Reinhardt "fpd833" wrote: I was graciously provided a macro that deletes all rows not having certain values in col A. In the following line: 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. I have pasted the provided code below, should the above make no sense. Thank you for your help!! Ken 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 With ActiveSheet .Select ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView .DisplayPageBreaks = False Firstrow = .UsedRange.Cells(7).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row ArrNames = Array(" Agt ID", "Agent Summary") For Lrow = Lastrow To Firstrow Step -1 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code modification?
Thanks Barb! How/where would I work that into the code?
"Barb Reinhardt" wrote: I believe you could use the LIKE operator to do this. If YourString like "ABC*" 'Finds cells that start with ABC. If YourString like "*ABC*" 'Finds cells with ABC anywhere in them -- HTH, Barb Reinhardt "fpd833" wrote: I was graciously provided a macro that deletes all rows not having certain values in col A. In the following line: 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. I have pasted the provided code below, should the above make no sense. Thank you for your help!! Ken 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 With ActiveSheet .Select ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView .DisplayPageBreaks = False Firstrow = .UsedRange.Cells(7).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row ArrNames = Array(" Agt ID", "Agent Summary") For Lrow = Lastrow To Firstrow Step -1 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
modification to this code | Excel Discussion (Misc queries) | |||
Code modification help | Excel Worksheet Functions | |||
Code modification help please | Excel Programming | |||
Modification in the CODE to HIDE rows and columns that start with ZERO (code given) | Excel Programming | |||
modification for the code | Excel Programming |