samsonjonesuk
New Member
- Joined
- Aug 24, 2005
- Messages
- 18
Hi Board
Been a while since I was last here and this is my problem if anyone can help.
I have a simple deduplication macro (below) which runs through a worksheet and deletes subsequent rows via a loop.
The macro begins very efficiently and deletes very quickly, however this then slows down to a virtual halt and after debugging I have found that the line which contains this :-Rows(z & ":" & z + y - 1).Delete Shift:=xlUp
is hanging for ages until it actually decides to delete the selected area.
Does anyone know why this is, I have turned off screen updating and automatic calculation and it is still as slow.
Any help would be greatly appreciated.
Sean
__________________________________
Public x As Integer, y As Integer, data As String, z As Integer
'deduplicate open projects spreadsheet
Sub A_dedup()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
x = 2
y = 0
z = 0
Sheets("Open Projects").Select
Cells.Select
Selection.sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("D2") _
, Order2:=xlAscending, header:=xlYes, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
Do
Sheets("Open Projects").Range("A" & x).Select
Sheets("Open Projects").Range("AE" & x).Value = "=VLOOKUP(B" & x & ",'Comp List'!A$2:B$254,2,0)"
data = Sheets("Open Projects").Range("A" & x).Value
If Sheets("Open Projects").Range("A" & x + 1) = data Then
If z = 0 Then
z = x + 1
End If
y = y + 1
Else
If y < 1 Then
Else
Rows(z & ":" & z + y - 1).Delete Shift:=xlUp
x = z - 1
z = 0
y = 0
End If
End If
x = x + 1
Loop While Range("A" & x).Value <> ""
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Been a while since I was last here and this is my problem if anyone can help.
I have a simple deduplication macro (below) which runs through a worksheet and deletes subsequent rows via a loop.
The macro begins very efficiently and deletes very quickly, however this then slows down to a virtual halt and after debugging I have found that the line which contains this :-Rows(z & ":" & z + y - 1).Delete Shift:=xlUp
is hanging for ages until it actually decides to delete the selected area.
Does anyone know why this is, I have turned off screen updating and automatic calculation and it is still as slow.
Any help would be greatly appreciated.
Sean
__________________________________
Public x As Integer, y As Integer, data As String, z As Integer
'deduplicate open projects spreadsheet
Sub A_dedup()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
x = 2
y = 0
z = 0
Sheets("Open Projects").Select
Cells.Select
Selection.sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("D2") _
, Order2:=xlAscending, header:=xlYes, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
Do
Sheets("Open Projects").Range("A" & x).Select
Sheets("Open Projects").Range("AE" & x).Value = "=VLOOKUP(B" & x & ",'Comp List'!A$2:B$254,2,0)"
data = Sheets("Open Projects").Range("A" & x).Value
If Sheets("Open Projects").Range("A" & x + 1) = data Then
If z = 0 Then
z = x + 1
End If
y = y + 1
Else
If y < 1 Then
Else
Rows(z & ":" & z + y - 1).Delete Shift:=xlUp
x = z - 1
z = 0
y = 0
End If
End If
x = x + 1
Loop While Range("A" & x).Value <> ""
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub