Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping macro
I need to create a macro in Excel which will search for certain terms and
delete the rows they are found in. In some cases these are text items which do not change, in some cases date values in dd/mm/yyyy format, and in some cases text which begins with a given string. I need the macro to loop until the given search item has been found, so effectively the macro will run in three separate and concurrent loops. How do I do this? I have tried using the Do/Loop commands with no success... TIA. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping macro
Sorry, I should clarify. When I say "starts with a given string" I mean it's
in a format which is always prefixed with text and numbers, and then completes with a unique number. When I said I need the macro to loop until the item has been found, I meant *not found*. Clearly I have not woken up yet!! "Aaron Howe" wrote: I need to create a macro in Excel which will search for certain terms and delete the rows they are found in. In some cases these are text items which do not change, in some cases date values in dd/mm/yyyy format, and in some cases text which begins with a given string. I need the macro to loop until the given search item has been found, so effectively the macro will run in three separate and concurrent loops. How do I do this? I have tried using the Do/Loop commands with no success... TIA. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping macro
Test on a copy of your workbooj
Option Explicit Sub deleterows() Dim rngColA As Range Dim ipointer As Long Dim sSting As String sSting = "1/1/2007" 'Change "A" to the column your data in in you are looking to find Set rngColA = ActiveSheet.Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp)) 'Work backwards from bottom to top when deleting rows With rngColA For ipointer = .Rows.Count To 1 Step -1 If .Cells(ipointer) < sSting Then .Cells(ipointer).EntireRow.Delete End If Next ipointer End With End Sub "Aaron Howe" wrote: Sorry, I should clarify. When I say "starts with a given string" I mean it's in a format which is always prefixed with text and numbers, and then completes with a unique number. When I said I need the macro to loop until the item has been found, I meant *not found*. Clearly I have not woken up yet!! "Aaron Howe" wrote: I need to create a macro in Excel which will search for certain terms and delete the rows they are found in. In some cases these are text items which do not change, in some cases date values in dd/mm/yyyy format, and in some cases text which begins with a given string. I need the macro to loop until the given search item has been found, so effectively the macro will run in three separate and concurrent loops. How do I do this? I have tried using the Do/Loop commands with no success... TIA. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping macro
Thanks Mike!
Is there a way to specify the date as an integer rather than a fixed value? I.e. to remove *any* dates? "Mike" wrote: Test on a copy of your workbooj Option Explicit Sub deleterows() Dim rngColA As Range Dim ipointer As Long Dim sSting As String sSting = "1/1/2007" 'Change "A" to the column your data in in you are looking to find Set rngColA = ActiveSheet.Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp)) 'Work backwards from bottom to top when deleting rows With rngColA For ipointer = .Rows.Count To 1 Step -1 If .Cells(ipointer) < sSting Then .Cells(ipointer).EntireRow.Delete End If Next ipointer End With End Sub "Aaron Howe" wrote: Sorry, I should clarify. When I say "starts with a given string" I mean it's in a format which is always prefixed with text and numbers, and then completes with a unique number. When I said I need the macro to loop until the item has been found, I meant *not found*. Clearly I have not woken up yet!! "Aaron Howe" wrote: I need to create a macro in Excel which will search for certain terms and delete the rows they are found in. In some cases these are text items which do not change, in some cases date values in dd/mm/yyyy format, and in some cases text which begins with a given string. I need the macro to loop until the given search item has been found, so effectively the macro will run in three separate and concurrent loops. How do I do this? I have tried using the Do/Loop commands with no success... TIA. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping macro
You could use Greater or less then now
< Now "Aaron Howe" wrote: Thanks Mike! Is there a way to specify the date as an integer rather than a fixed value? I.e. to remove *any* dates? "Mike" wrote: Test on a copy of your workbooj Option Explicit Sub deleterows() Dim rngColA As Range Dim ipointer As Long Dim sSting As String sSting = "1/1/2007" 'Change "A" to the column your data in in you are looking to find Set rngColA = ActiveSheet.Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp)) 'Work backwards from bottom to top when deleting rows With rngColA For ipointer = .Rows.Count To 1 Step -1 If .Cells(ipointer) < sSting Then .Cells(ipointer).EntireRow.Delete End If Next ipointer End With End Sub "Aaron Howe" wrote: Sorry, I should clarify. When I say "starts with a given string" I mean it's in a format which is always prefixed with text and numbers, and then completes with a unique number. When I said I need the macro to loop until the item has been found, I meant *not found*. Clearly I have not woken up yet!! "Aaron Howe" wrote: I need to create a macro in Excel which will search for certain terms and delete the rows they are found in. In some cases these are text items which do not change, in some cases date values in dd/mm/yyyy format, and in some cases text which begins with a given string. I need the macro to loop until the given search item has been found, so effectively the macro will run in three separate and concurrent loops. How do I do this? I have tried using the Do/Loop commands with no success... TIA. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping macro
Using this then return a syntax error at "sSting < Now":
Option Explicit Sub deleterows() Dim rngColA As Range Dim ipointer As Long Dim sSting As String sSting < Now 'Change "A" to the column your data in in you are looking to find Set rngColA = ActiveSheet.Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp)) 'Work backwards from bottom to top when deleting rows With rngColA For ipointer = .Rows.Count To 1 Step -1 If .Cells(ipointer) < sSting Then .Cells(ipointer).EntireRow.Delete End If Next ipointer End With End Sub "Mike" wrote: You could use Greater or less then now < Now "Aaron Howe" wrote: Thanks Mike! Is there a way to specify the date as an integer rather than a fixed value? I.e. to remove *any* dates? "Mike" wrote: Test on a copy of your workbooj Option Explicit Sub deleterows() Dim rngColA As Range Dim ipointer As Long Dim sSting As String sSting = "1/1/2007" 'Change "A" to the column your data in in you are looking to find Set rngColA = ActiveSheet.Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp)) 'Work backwards from bottom to top when deleting rows With rngColA For ipointer = .Rows.Count To 1 Step -1 If .Cells(ipointer) < sSting Then .Cells(ipointer).EntireRow.Delete End If Next ipointer End With End Sub "Aaron Howe" wrote: Sorry, I should clarify. When I say "starts with a given string" I mean it's in a format which is always prefixed with text and numbers, and then completes with a unique number. When I said I need the macro to loop until the item has been found, I meant *not found*. Clearly I have not woken up yet!! "Aaron Howe" wrote: I need to create a macro in Excel which will search for certain terms and delete the rows they are found in. In some cases these are text items which do not change, in some cases date values in dd/mm/yyyy format, and in some cases text which begins with a given string. I need the macro to loop until the given search item has been found, so effectively the macro will run in three separate and concurrent loops. How do I do this? I have tried using the Do/Loop commands with no success... TIA. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping macro
Don't worry, I found another way to do it. It's dirty and it doesn't look
very nice but it works. I edited the macro to replace anything with the string "*/2007" to "2007", which in turn gave every affected cell the same date. From there I could use that as a string which I could remove using the existing macro and an ElseIf statement. "Mike" wrote: You could use Greater or less then now < Now "Aaron Howe" wrote: Thanks Mike! Is there a way to specify the date as an integer rather than a fixed value? I.e. to remove *any* dates? "Mike" wrote: Test on a copy of your workbooj Option Explicit Sub deleterows() Dim rngColA As Range Dim ipointer As Long Dim sSting As String sSting = "1/1/2007" 'Change "A" to the column your data in in you are looking to find Set rngColA = ActiveSheet.Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp)) 'Work backwards from bottom to top when deleting rows With rngColA For ipointer = .Rows.Count To 1 Step -1 If .Cells(ipointer) < sSting Then .Cells(ipointer).EntireRow.Delete End If Next ipointer End With End Sub "Aaron Howe" wrote: Sorry, I should clarify. When I say "starts with a given string" I mean it's in a format which is always prefixed with text and numbers, and then completes with a unique number. When I said I need the macro to loop until the item has been found, I meant *not found*. Clearly I have not woken up yet!! "Aaron Howe" wrote: I need to create a macro in Excel which will search for certain terms and delete the rows they are found in. In some cases these are text items which do not change, in some cases date values in dd/mm/yyyy format, and in some cases text which begins with a given string. I need the macro to loop until the given search item has been found, so effectively the macro will run in three separate and concurrent loops. How do I do this? I have tried using the Do/Loop commands with no success... TIA. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping macro
Needs to be
sSting = < Now "Aaron Howe" wrote: Using this then return a syntax error at "sSting < Now": Option Explicit Sub deleterows() Dim rngColA As Range Dim ipointer As Long Dim sSting As String sSting < Now 'Change "A" to the column your data in in you are looking to find Set rngColA = ActiveSheet.Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp)) 'Work backwards from bottom to top when deleting rows With rngColA For ipointer = .Rows.Count To 1 Step -1 If .Cells(ipointer) < sSting Then .Cells(ipointer).EntireRow.Delete End If Next ipointer End With End Sub "Mike" wrote: You could use Greater or less then now < Now "Aaron Howe" wrote: Thanks Mike! Is there a way to specify the date as an integer rather than a fixed value? I.e. to remove *any* dates? "Mike" wrote: Test on a copy of your workbooj Option Explicit Sub deleterows() Dim rngColA As Range Dim ipointer As Long Dim sSting As String sSting = "1/1/2007" 'Change "A" to the column your data in in you are looking to find Set rngColA = ActiveSheet.Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp)) 'Work backwards from bottom to top when deleting rows With rngColA For ipointer = .Rows.Count To 1 Step -1 If .Cells(ipointer) < sSting Then .Cells(ipointer).EntireRow.Delete End If Next ipointer End With End Sub "Aaron Howe" wrote: Sorry, I should clarify. When I say "starts with a given string" I mean it's in a format which is always prefixed with text and numbers, and then completes with a unique number. When I said I need the macro to loop until the item has been found, I meant *not found*. Clearly I have not woken up yet!! "Aaron Howe" wrote: I need to create a macro in Excel which will search for certain terms and delete the rows they are found in. In some cases these are text items which do not change, in some cases date values in dd/mm/yyyy format, and in some cases text which begins with a given string. I need the macro to loop until the given search item has been found, so effectively the macro will run in three separate and concurrent loops. How do I do this? I have tried using the Do/Loop commands with no success... TIA. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looping Macro | Excel Discussion (Misc queries) | |||
Looping macro | Excel Worksheet Functions | |||
Looping a macro | Excel Discussion (Misc queries) | |||
Looping macro | Excel Programming | |||
Need help with my looping macro | Excel Programming |