Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 Questions about the following code - Please help if you can.
"malycom" wrote in message ... Hi all and Happy New Year to you. I have a spreadsheet which compares to sets of data and aligns them correctly. This works fine but it takes a long time to run as there are 1500 rows there about. THis is the code =========================================== Sub Macro1() Dim myCell As Range Dim row As Long, i As Long Range("A:J").Sort Key1:=Range("A2"), order1:=xlAscending, header:=xlNo Range("L:R").Sort Key1:=Range("L2"), order1:=xlAscending, header:=xlNo row = 1 Do Until IsEmpty(Cells(row, "A")) And IsEmpty(Cells(row, "L")) If Cells(row, 12).Value < Cells(row, 1).Value And Cells(row, 1).Value < Cells(row, 12).Value Then Cells(row, 12).Insert Shift:=xlDown Cells(row, 13).Insert Shift:=xlDown Cells(row, 14).Insert Shift:=xlDown Cells(row, 15).Insert Shift:=xlDown Cells(row, 16).Insert Shift:=xlDown Cells(row, 17).Insert Shift:=xlDown Cells(row, 18).Insert Shift:=xlDown Else If Cells(row, 1).Value < Cells(row, 12).Value And Cells(row, 12).Value < Cells(row, 1).Value Then Cells(row, 2).Insert Shift:=xlDown Cells(row, 3).Insert Shift:=xlDown Cells(row, 4).Insert Shift:=xlDown Cells(row, 5).Insert Shift:=xlDown Cells(row, 6).Insert Shift:=xlDown Cells(row, 7).Insert Shift:=xlDown Cells(row, 8).Insert Shift:=xlDown Cells(row, 9).Insert Shift:=xlDown Cells(row, 10).Insert Shift:=xlDown End If End If row = row + 1 Loop End Sub ================================================== = Question 1: Can someone tell me how I can add some form of progress bar or visual aid that will tell me the process completed at any point during running the application. Question 2: Is there a quicker way to achieve the above code. (Question 1 is more important unless question 2 is achieved easily) Please note, the code I use was from this community in the first place so I will need detailed explanations on how to achieve either of the above questions. I am not a coder and rely on the bright people out there for help with this type of stuff. This community has answered every single question I have asked in the past and I am in no doubt that I will get the answer for this so thanks in advance for all who reply. Kind regards Malcolm For a start try putting application.ScreenUpdating = False application.Calculation = xlCalculationManual at the start of the code and application.ScreenUpdating = True application.Calculation = xlCalculationAutomatic at the end. This should speed it up a bit. -- Chris Lewis |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 Questions about code with excel 97 | Excel Programming | |||
2 Questions about code with excel 97 | Excel Programming | |||
Rounding questions: (Formula and Code) ? | Excel Programming | |||
Urgent questions regarding the code below | Excel Programming | |||
Two Questions about the Following Block of Code | Excel Programming |