Program takes long to load

[genaro]

my program takes time to load, it there a way make this program faster by changing part of the code?



Sub project_input()

Dim UsdRws As Long
Dim i As Long

Application.ScreenUpdating = False

UsdRws = Range("A1").CurrentRegion.Rows.Count

For i = UsdRws To 2 Step -1

If Range("M" & i).Value Like "Complete" Then
Rows(i).Copy Sheets("Complete").Range("A" & Rows.Count).End(xlUp).Offset(1)
Rows(i).Delete
End If

Next i

Application.ScreenUpdating = True

'------------------------------------------------------

UserForm2.Show
''
Application.ScreenUpdating = False
Cells(2, 11).AutoFill Destination:=Range(Cells(2, 11), Cells(500, 11)), Type:=PasteSpecial
Cells(2, 12).AutoFill Destination:=Range(Cells(2, 12), Cells(500, 12)), Type:=PasteSpecial
Cells(2, 13).AutoFill Destination:=Range(Cells(2, 13), Cells(500, 13)), Type:=PasteSpecial
Application.ScreenUpdating = True


End Sub

also in the line Rows(i).Delete

is there a way to only delete part of the row from A:J insted of all the row?

Thank you

[Logit]

Interesting

Can you post your workbook absent confidential information?

[jolivanes]

In the AutoFill part, how did you arrive at 500?
When you say it takes along time to load, what are you referring to? That it takes a long time before the code is finished running?

[jolivanes]

Is this anywhere near to what you have in mind?
If you think it is, make sure you try it on a copy of your workbook first.
There is no "Undo" for a macro.

Sub Maybe()
Dim lr As Long, lc As Long
lr = Cells.Find("*", , , , xlByRows, xlPrevious).Row
lc = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
Application.ScreenUpdating = False
With Columns(13)
.AutoFilter 1, "Complete"
With Range(Cells(2, 1), Cells(lr, lc)).SpecialCells(12)
.Copy Sheets("Complete").Cells(Rows.Count, 1).End(xlUp).Offset(1)
.Delete Shift:=xlUp
End With
.AutoFilter
End With
With Range(Cells(2, 11), Cells(2, 13))
.AutoFill Destination:=.Resize(lr - 1), Type:=xlFillSeries
End With
Application.ScreenUpdating = True
End Sub

[genaro]

i think that the problem is that when the autofill is done for the 500 rows, the next time the program is run it checks all those rows. i dont know if by adding something else if the if statement like to check column M for Complete and also check Column A for not blank. i just know how to write in the code i try------
If Range("A" & i).Value Like "the cells is not blank" & Range("M" & i).Value Like "Complete" Then

but it didn't work, i don't know if i can do that.

[Dim UsdRws As LongDim i As Long


Application.ScreenUpdating = False


UsdRws = Range("A1").CurrentRegion.Rows.Count

For i = UsdRws To 2 Step -1


If Range("M" & i).Value Like "Complete" Then
Rows(i).Copy Sheets("Complete").Range("A" & Rows.Count).End(xlUp).Offset(1)
Rows(i).Delete
End If

Next i






Application.ScreenUpdating = True

[genaro]

Is this anywhere near to what you have in mind?
If you think it is, make sure you try it on a copy of your workbook first.
There is no "Undo" for a macro.

Sub Maybe()
Dim lr As Long, lc As Long
lr = Cells.Find("*", , , , xlByRows, xlPrevious).Row
lc = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
Application.ScreenUpdating = False
With Columns(13)
.AutoFilter 1, "Complete"
With Range(Cells(2, 1), Cells(lr, lc)).SpecialCells(12)
.Copy Sheets("Complete").Cells(Rows.Count, 1).End(xlUp).Offset(1)
.Delete Shift:=xlUp
End With
.AutoFilter
End With
With Range(Cells(2, 11), Cells(2, 13))
.AutoFill Destination:=.Resize(lr - 1), Type:=xlFillSeries
End With
Application.ScreenUpdating = True
End Sub

[snb]

Please do not quote and please use code tags !!

[jolivanes]

Re: "i think that the problem is ........."
If you think you know what the problem is, you should be able to fix it.