Macro deletes row in range, macro then skips the row moved up

[steven.holloway]

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

 

[Mike H]

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

 

[Mike H]

Hi that should be


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


Mike

 

[steven.holloway]

Thanks Mike, that worked a treat!

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

 

[Mike H]

I'm glad that worked and thank for the feedback.

Building a new range would generally be my preferred method but others may
disagree. To run backwards you would do something like this

Sub delete_Me()
Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
For x = Lastrow To 1 Step -1
If Cells(x, 1).Value = 0 Then
Rows(x).Delete
End If
Next
End Sub

Mike

 

[steven.holloway]

Mike you are genius!

I have another question, which is more around being more efficient with VB
programming. I have the following code which I would like to simplyfy, I know
in Excel I could use the "INDIRECT" formula to make the period part of the
named worksheet a variable for example INDIRECT("PeopleSoft -
"&Current_Month), can something similar be done in VB?

If Current_Month = "P01" Then
Sheets("PeopleSoft - P01").Select
Else
If Current_Month = "P02" Then
Sheets("PeopleSoft - P02").Select
Else
If Current_Month = "P03" Then
Sheets("PeopleSoft - P03").Select
Else
If Current_Month = "P04" Then
Sheets("PeopleSoft - P04").Select
Else
If Current_Month = "P05" Then
Sheets("PeopleSoft - P05").Select
Else
If Current_Month = "P06" Then
Sheets("PeopleSoft - P06").Select
Else
If Current_Month = "P07" Then
Sheets("PeopleSoft - P07").Select
Else
If Current_Month = "P08" Then
Sheets("PeopleSoft - P08").Select
Else
If Current_Month = "P09" Then
Sheets("PeopleSoft - P09").Select
Else
If Current_Month = "P10" Then
Sheets("PeopleSoft - P10").Select
Else
If Current_Month = "P11" Then
Sheets("PeopleSoft -
P11").Select
Else
If Current_Month = "P12" Then
Sheets("PeopleSoft -
P12").Select
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

 

[Mike H]

Hi,

I'd use Select Case

Sub simpler()
Select Case current_month
Case "P01"
mysheet = "PeopleSoft - P01"
Case "P02"
mysheet = "PeopleSoft - P02"
Case Else
'do something else
End Select
Sheets(mysheet).Select
End Sub

Mike

 

[steven.holloway]

This worked a treat too - (I did not know the Case command)

Mike you have been a star! Not only have you provided exactly what I needed
first time every time, the speed at which you have responded has been
exceptional.

Thank you for your first class service.
Steve

 

[Mike H]

Glad I could be of help

Mike

This worked a treat too - (I did not know the Case command)

Mike you have been a star! Not only have you provided exactly what I needed
first time every time, the speed at which you have responded has been
exceptional.

Thank you for your first class service.
Steve