Ideas to make this faster

M

mnordeen

Board Regular
Joined
Mar 27, 2006
Messages
161
Any tricks anyone has to make this run faster?
Code: 
Private Sub Consolidate_Click()
Dim LastRow As Long
Dim i As Long
Dim endall As Long
Worksheets("Wait_Display").Visible = xlSheetVisible
Worksheets("Wait_Display").Select
Application.ScreenUpdating = False
Worksheets("DB_Inv_Item_List").Select
Range("CC7:CJ5000").Select
    Selection.ClearContents
Worksheets("DB_Inv_Item_List").Cells(2, 4).Value = Now()
'Catagory 100
endall = Worksheets("DB_Inv_Item_List").Range("g5").Value + 7 'Ref a count of how many cells are being actually used
For i = 7 To endall
LastRow = Worksheets("DB_Inv_Item_List").Range("cc65536").End(xlUp).Row + 1
If Sheets("DB_Inv_Item_List").Cells(i, 1).Value = "ACTIVE" Then
Worksheets("DB_Inv_Item_List").Cells(LastRow, 81).Value = Worksheets("DB_Inv_Item_List").Cells(i, 2).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 82).Value = Worksheets("DB_Inv_Item_List").Cells(i, 3).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 83).Value = Worksheets("DB_Inv_Item_List").Cells(i, 4).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 84).Value = Worksheets("DB_Inv_Item_List").Cells(i, 5).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 85).Value = Worksheets("DB_Inv_Item_List").Cells(i, 6).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 86).Value = Worksheets("DB_Inv_Item_List").Cells(i, 7).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 87).Value = Worksheets("DB_Inv_Item_List").Cells(i, 8).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 88).Value = Worksheets("DB_Inv_Item_List").Cells(i, 9).Value
Else
End If
Next i
'Catagory 200
endall = Worksheets("DB_Inv_Item_List").Range("q5").Value + 7
For i = 7 To endall
LastRow = Worksheets("DB_Inv_Item_List").Range("cc65536").End(xlUp).Row + 1
If Sheets("DB_Inv_Item_List").Cells(i, 11).Value = "ACTIVE" Then
Worksheets("DB_Inv_Item_List").Cells(LastRow, 81).Value = Worksheets("DB_Inv_Item_List").Cells(i, 12).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 82).Value = Worksheets("DB_Inv_Item_List").Cells(i, 13).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 83).Value = Worksheets("DB_Inv_Item_List").Cells(i, 14).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 84).Value = Worksheets("DB_Inv_Item_List").Cells(i, 15).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 85).Value = Worksheets("DB_Inv_Item_List").Cells(i, 16).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 86).Value = Worksheets("DB_Inv_Item_List").Cells(i, 17).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 87).Value = Worksheets("DB_Inv_Item_List").Cells(i, 18).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 88).Value = Worksheets("DB_Inv_Item_List").Cells(i, 19).Value
Else
End If
Next i
'Catagory 300
endall = Worksheets("DB_Inv_Item_List").Range("aa5").Value + 7
For i = 7 To endall
LastRow = Worksheets("DB_Inv_Item_List").Range("cc65536").End(xlUp).Row + 1
If Sheets("DB_Inv_Item_List").Cells(i, 21).Value = "ACTIVE" Then
Worksheets("DB_Inv_Item_List").Cells(LastRow, 81).Value = Worksheets("DB_Inv_Item_List").Cells(i, 22).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 82).Value = Worksheets("DB_Inv_Item_List").Cells(i, 23).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 83).Value = Worksheets("DB_Inv_Item_List").Cells(i, 24).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 84).Value = Worksheets("DB_Inv_Item_List").Cells(i, 25).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 85).Value = Worksheets("DB_Inv_Item_List").Cells(i, 26).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 86).Value = Worksheets("DB_Inv_Item_List").Cells(i, 27).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 87).Value = Worksheets("DB_Inv_Item_List").Cells(i, 28).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 88).Value = Worksheets("DB_Inv_Item_List").Cells(i, 29).Value
Else
End If
Next i
'Catagory 400
endall = Worksheets("DB_Inv_Item_List").Range("ak5").Value + 7
For i = 7 To endall
LastRow = Worksheets("DB_Inv_Item_List").Range("cc65536").End(xlUp).Row + 1
If Sheets("DB_Inv_Item_List").Cells(i, 31).Value = "ACTIVE" Then
Worksheets("DB_Inv_Item_List").Cells(LastRow, 81).Value = Worksheets("DB_Inv_Item_List").Cells(i, 32).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 82).Value = Worksheets("DB_Inv_Item_List").Cells(i, 33).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 83).Value = Worksheets("DB_Inv_Item_List").Cells(i, 34).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 84).Value = Worksheets("DB_Inv_Item_List").Cells(i, 35).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 85).Value = Worksheets("DB_Inv_Item_List").Cells(i, 36).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 86).Value = Worksheets("DB_Inv_Item_List").Cells(i, 37).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 87).Value = Worksheets("DB_Inv_Item_List").Cells(i, 38).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 88).Value = Worksheets("DB_Inv_Item_List").Cells(i, 39).Value
Else
End If
Next i
'Catagory 500
endall = Worksheets("DB_Inv_Item_List").Range("au5").Value + 7
For i = 7 To endall
LastRow = Worksheets("DB_Inv_Item_List").Range("cc65536").End(xlUp).Row + 1
If Sheets("DB_Inv_Item_List").Cells(i, 41).Value = "ACTIVE" Then
Worksheets("DB_Inv_Item_List").Cells(LastRow, 81).Value = Worksheets("DB_Inv_Item_List").Cells(i, 42).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 82).Value = Worksheets("DB_Inv_Item_List").Cells(i, 43).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 83).Value = Worksheets("DB_Inv_Item_List").Cells(i, 44).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 84).Value = Worksheets("DB_Inv_Item_List").Cells(i, 45).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 85).Value = Worksheets("DB_Inv_Item_List").Cells(i, 46).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 86).Value = Worksheets("DB_Inv_Item_List").Cells(i, 47).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 87).Value = Worksheets("DB_Inv_Item_List").Cells(i, 48).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 88).Value = Worksheets("DB_Inv_Item_List").Cells(i, 49).Value
Else
End If
Next i
'Catagory 600
endall = Worksheets("DB_Inv_Item_List").Range("be5").Value + 7
For i = 7 To endall
LastRow = Worksheets("DB_Inv_Item_List").Range("cc65536").End(xlUp).Row + 1
If Sheets("DB_Inv_Item_List").Cells(i, 51).Value = "ACTIVE" Then
Worksheets("DB_Inv_Item_List").Cells(LastRow, 81).Value = Worksheets("DB_Inv_Item_List").Cells(i, 52).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 82).Value = Worksheets("DB_Inv_Item_List").Cells(i, 53).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 83).Value = Worksheets("DB_Inv_Item_List").Cells(i, 54).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 84).Value = Worksheets("DB_Inv_Item_List").Cells(i, 55).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 85).Value = Worksheets("DB_Inv_Item_List").Cells(i, 56).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 86).Value = Worksheets("DB_Inv_Item_List").Cells(i, 57).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 87).Value = Worksheets("DB_Inv_Item_List").Cells(i, 58).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 88).Value = Worksheets("DB_Inv_Item_List").Cells(i, 59).Value
Else
End If
Next i
'Catagory 700
endall = Worksheets("DB_Inv_Item_List").Range("bo5").Value + 7
For i = 7 To endall
LastRow = Worksheets("DB_Inv_Item_List").Range("cc65536").End(xlUp).Row + 1
If Sheets("DB_Inv_Item_List").Cells(i, 61).Value = "ACTIVE" Then
Worksheets("DB_Inv_Item_List").Cells(LastRow, 81).Value = Worksheets("DB_Inv_Item_List").Cells(i, 2).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 82).Value = Worksheets("DB_Inv_Item_List").Cells(i, 3).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 83).Value = Worksheets("DB_Inv_Item_List").Cells(i, 4).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 84).Value = Worksheets("DB_Inv_Item_List").Cells(i, 5).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 85).Value = Worksheets("DB_Inv_Item_List").Cells(i, 6).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 86).Value = Worksheets("DB_Inv_Item_List").Cells(i, 7).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 87).Value = Worksheets("DB_Inv_Item_List").Cells(i, 8).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 88).Value = Worksheets("DB_Inv_Item_List").Cells(i, 9).Value
Else
End If
Next i
'Catagory 800
endall = Worksheets("DB_Inv_Item_List").Range("by5").Value + 7
For i = 7 To endall
LastRow = Worksheets("DB_Inv_Item_List").Range("cc65536").End(xlUp).Row + 1
If Sheets("DB_Inv_Item_List").Cells(i, 71).Value = "ACTIVE" Then
Worksheets("DB_Inv_Item_List").Cells(LastRow, 81).Value = Worksheets("DB_Inv_Item_List").Cells(i, 2).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 82).Value = Worksheets("DB_Inv_Item_List").Cells(i, 3).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 83).Value = Worksheets("DB_Inv_Item_List").Cells(i, 4).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 84).Value = Worksheets("DB_Inv_Item_List").Cells(i, 5).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 85).Value = Worksheets("DB_Inv_Item_List").Cells(i, 6).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 86).Value = Worksheets("DB_Inv_Item_List").Cells(i, 7).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 87).Value = Worksheets("DB_Inv_Item_List").Cells(i, 8).Value
Worksheets("DB_Inv_Item_List").Cells(LastRow, 88).Value = Worksheets("DB_Inv_Item_List").Cells(i, 9).Value
Else
End If
Next i
ActiveWorkbook.Save
Application.ScreenUpdating = True
Sheets("DB_Inv_Item_List").Select
Worksheets("Wait_Display").Visible = xlSheetHidden
End Sub
 
Sort by date Sort by votes
It's just trying to take advantage of the regular 10 column offset between category data and looping through common code rather than repeating blocks of similar code.

Bear in mind that it was late, based on my assumption of the data layout and I was just typing and never ran it to test any aspect.
 
Upvote 0

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
You must log in or register to reply here.

Similar threads

P
  • Solved
Type mis match & is there a quicker way?
Replies
4
Views
110
mumps
mumps
M
  • Solved
Trim/Clean Rows faster in VBA
Replies
3
Views
251
mnh
M
I
  • Question
Resize property when transferring data over
2
Replies
17
Views
338
ItalianPlatinum
I
T
  • Question
How to show Msg if Record not found in table
Replies
3
Views
168
myall_blues
myall_blues
S
  • Question
CSV export to remove column
Replies
1
Views
230
StellarRumble59
S

Forum statistics

Threads
1,216,434
Messages
6,130,611
Members
449,584
Latest member
c_clark

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top