Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Macro deletes row in range, macro then skips the row moved up
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 26
Default Macro deletes row in range, macro then skips the row moved up
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 11,501
Default Macro deletes row in range, macro then skips the row moved up
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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 11,501
Default Macro deletes row in range, macro then skips the row moved up
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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 26
Default Macro deletes row in range, macro then skips the row moved up
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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 11,501
Default Macro deletes row in range, macro then skips the row moved up
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" wrote:

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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 26
Default Macro deletes row in range, macro then skips the row moved up
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" wrote:

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" wrote:

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
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 11,501
Default Macro deletes row in range, macro then skips the row moved up
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" wrote:

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" wrote:

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" wrote:

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
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 26
Default Macro deletes row in range, macro then skips the row moved up
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" wrote:

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" wrote:

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" wrote:

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" wrote:

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
Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 11,501
Default Macro deletes row in range, macro then skips the row moved up
Glad I could be of help

Mike

"steven.holloway" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
MACRO which deletes a row, when it finds a specific number Spiros Excel Discussion (Misc queries) 3 December 4th 07 05:43 AM
Excel Charting Macro That deletes Sheet Dave_Tho1968 Excel Discussion (Misc queries) 3 October 15th 07 09:02 PM
Macro that deletes rows from cell containing End to end of data. Richard Excel Discussion (Misc queries) 2 July 25th 07 03:51 PM
Macro that deletes every third row....+ ajjag Excel Discussion (Misc queries) 4 June 27th 06 06:03 PM
Renaming and saving workbooks but macro still deletes shrek Excel Worksheet Functions 0 November 8th 05 05:43 PM


All times are GMT +1. The time now is 01:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement