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.

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.

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.

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.

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.

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.

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.

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.