Thanks Mike, that worked a treat!

Just for information, do you know how to make the macro run through the
range backwards?


"Mike H" wrote:

Hi that should be


Set copyrange = Term_Date.entirerow
Set copyrange = Union(copyrange, Term_Date.entirerow)


Mike
"Mike H" wrote:

Hi,

There are a couple of ways to address this, one is to work backwards and
another is as in this example where you build up a new range and delete it
all in one go. Not tested but this should work

Sub PeopleSoft_Conversion()
Dim copyrange As Range
With Sheets("SELECTIONS")
Last_Year_End = .Range("E18")
Current_Month = .Range("D4")
End With

If Current_Month = "P01" Then
Sheets("PeopleSoft - P01").Select
Range("B2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Sheets("CONVERSION").Select
Range("A1").Select
ActiveSheet.Paste

With Sheets("CONVERSION")
First_Term_Date = "BU2"
Last_Row = .Range("A1").Offset(0, 0).End(xlDown).Row
Set Term_Date_Range = .Range(.Range(First_Term_Date).Offset(0,
0), .Cells(Last_Row, "BU"))
End With

For Each Term_Date In Term_Date_Range
If Term_Date <= Last_Year_End Then
If Term_Date < "" Then

'************
'*** New Code
If copyrange Is Nothing Then
Set copyrange = Term_Date
Else
Set copyrange = Union(copyrange, Term_Date)
End If

'************

' With Sheets("CONVERSION")
' Term_Date.EntireRow.Delete
'End With

End If
End If
Next Term_Date
End If

'*** New code
If Not copyrange Is Nothing Then
copyrange.Delete
End If
End Sub


"steven.holloway" wrote:

Hi I hope someone can help with this request!

I have the following Macro as work in progress, basically it checks each
cell in a range for a given criteria and if found deletes the row. However
due to the nature of Excel, once a row is deleted the rows are shifted up and
so that row shifted up is missed in the "Next" command as it is moved to the
existing cell in the range.

Is there any way of making the macro start at the last cell in the range and
work upwards (which will eliminate the shifting up scenario) or getting the
macro to repeat the the same cell in the range if it does delete a row?

Many thanks in advance

Sub PeopleSoft_Conversion()

With Sheets("SELECTIONS")
Last_Year_End = .Range("E18")
Current_Month = .Range("D4")
End With

If Current_Month = "P01" Then
Sheets("PeopleSoft - P01").Select
Range("B2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Sheets("CONVERSION").Select
Range("A1").Select
ActiveSheet.Paste

With Sheets("CONVERSION")
First_Term_Date = "BU2"
Last_Row = .Range("A1").Offset(0, 0).End(xlDown).Row
Set Term_Date_Range = .Range(.Range(First_Term_Date).Offset(0,
0), .Cells(Last_Row, "BU"))
End With

For Each Term_Date In Term_Date_Range
If Term_Date <= Last_Year_End Then
If Term_Date < "" Then
With Sheets("CONVERSION")
Term_Date.EntireRow.Delete
End With

End If
End If
Next Term_Date
End If

End Sub