Slow macro Excel 2007

[SabCie]

Hi,

I have a simple macro that was built in Excel 2003. The macro simply takes the value of a cell and output it in another sheet. This runs for about 1000 lines.

This used to take 5 sec with Excel 2003 but now takes forever with Excel 2007.

Anyone can suggest an idea? I tried setting the calculation mode to manual and disabling the screen updating but it is still extremely slow.

Thanks

 

[diddi]

post your code

 

[Norie]

Do you mean a thousand lines of code or a a thousand rows?

 

[diddi]

i was wondering if they have used .select all over the place?

 

[SabCie]

This is my code:

i = 5
j = 18
While Sheets("Data1").Cells(i, "A") <> ""
Test = Sheets("Data1").Range("c" & i).Value
Select Case Test
Case "A"
Cells(j, "A").Value = Sheets("Data1").Cells(i, "A").Value
Cells(j, "B").Value = Sheets("Data1").Cells(i, "B").Value
Cells(j, "C").Value = Sheets("Data1").Cells(i, "C").Value
Cells(j, "D").Value = Sheets("Data1").Cells(i, "D").Value
Cells(j, "E").Value = Sheets("Data1").Cells(i, "N").Value
sum1 = sum1 + Sheets("Data1").Cells(i, "N").Value
j = j + 1
End Select
i = i + 1
Wend

Thanks

 

[diddi]

is that the whole subroutine?

 

[SabCie]

It is not the whole code but it is the loop portion that takes forever.

 

[diddi]

try this...

i = 5
With Sheets("Data")
    For j= 18 to .Cells(Rows.Count, 1).End(xlUp).Row 
        Test = .Range("c" & i).Value
        Select Case Test
            Case "A"
                Cells(j, "A").Value = .Cells(i, "A").Value
                Cells(j, "B").Value = .Cells(i, "B").Value
                Cells(j, "C").Value = .Cells(i, "C").Value
                Cells(j, "D").Value = .Cells(i, "D").Value
                Cells(j, "E").Value = .Cells(i, "N").Value
                sum1 = sum1 + .Cells(i, "N").Value
        End Select
        i = i + 1
    next j
end with

 

[diddi]

im not sure that the logic in your code is correct. what are you actually trying to achieve in this loop

 

[SabCie]

Thank you diddi,

I will try your suggestion. The goal of that code is to browse the column c of the sheet Data1 and to only paste informations if the value of the cell is ''A'', otherwise skip. It is very basic.

I am just really surprised that in Excel 2003 this used to take seconds and now take at least 10 minutes. Do you know why? Is there anything I have to do in the settings of Excel 2007?

Thank you very much