Thread: simplify code
View Single Post
  #2   Report Post  
Dave O
 
Posts: n/a
Default

Looks like you can condense it like this:

LastRow = ActiveSheet.Cells(Rows.Count, "b").End(xlUp).Row
Range("E1").Select
For j=1 To LastRow
If Range("b" & j) < Range("a" & j) Then
Range("b" & j).Select
Selection.Insert Shift:=xlDown
Range("c" & j).Select
Selection.Insert Shift:=xlDown
Range("d" & j).Select
Selection.Insert Shift:=xlDown
Range("e" & j).Select
Next j

I suspect the length of time required to run your code is not due to
the quantity of IF statements, however. More likely the duration is
due to the spreadsheet recalculating itself every time one of the
insert / shift down operations is performed. You might try the
following code at the beginning of your macro to turn calculation from
auto to manual:

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

.... and then this code at the end to turn calculation back to auto:
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With