Excel is not responding when executing code

Brew

Well-known Member
Joined
Sep 29, 2003
Messages
1,569
The following code works if my source records from c6:e255, but when I added more records, (c6:e365), excel locks up and is not responding:
Sub Conversion()
Dim i As Long
Application.ScreenUpdating = False
Range("g5:i" & Rows.Count).ClearContents
Range("g5").Resize(, 3).Value = Array("h1", "h2", "h3")
For i = 6 To Range("c" & Rows.Count).End(xlUp).Row
Range("g" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Cells(i, "c"), Cells(i, "d"), Cells(i, "e"))
Range("g" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Cells(i, "c"), Cells(i, "e"), Cells(i, "d"))
Range("g" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Cells(i, "d"), Cells(i, "c"), Cells(i, "e"))
Range("g" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Cells(i, "d"), Cells(i, "e"), Cells(i, "c"))
Range("g" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Cells(i, "e"), Cells(i, "c"), Cells(i, "d"))
Range("g" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Cells(i, "e"), Cells(i, "d"), Cells(i, "c"))
Next
Range("g5").Resize(, 3).ClearContents
Application.ScreenUpdating = True
End Sub
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
I'm not sure of this, but, 265-5 = 251. And, from the Worksheet and workbook specifications maximums, I read: "Maximum number of named views in a workbook: Limited by available memory; a summary report shows only the first 251 scenarios". Could this be the reason for your problem?
 

Brew

Well-known Member
Joined
Sep 29, 2003
Messages
1,569
That was my concern. I though it might be a memory issue....Is there around it.
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
Brew, maybe you could do your present code as is, but repeat the essential code for the next 251 cells or less, just before the End Sub. Give it a try, and let us know if it works. Sounds logical to me...
 

Brew

Well-known Member
Joined
Sep 29, 2003
Messages
1,569

ADVERTISEMENT

How do I express the repeat of the essential code within the existing code?
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Brew, your code works fine for me, and I filled cols C:E down to row 455.

Although I can't work out what you are doing with this line of code:
Code:
Range("g5").Resize(, 3).Value = Array("h1", "h2", "h3")
as you do nothing with these cells, and then clear them again after the main body of the code.
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829

ADVERTISEMENT

Seems as if Glenn is going to be able to solve your problem for you! Good luck.

What I had in mind was:
Start with your original code, up to the end of the "For-Next" loop
Code:
Sub Conversion() 
Dim i As Long 
Application.ScreenUpdating = False 
Range("g5:i" & Rows.Count).ClearContents 
Range("g5").Resize(, 3).Value = Array("h1", "h2", "h3") 
For i = 6 To 251  'CHANGE THIS LIMIT TO 251 HERE 
   Range("g" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Cells(i, "c"), Cells(i, "d"), Cells(i, "e")) 
   Range("g" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Cells(i, "c"), Cells(i, "e"), Cells(i, "d")) 
   Range("g" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Cells(i, "d"), Cells(i, "c"), Cells(i, "e")) 
   Range("g" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Cells(i, "d"), Cells(i, "e"), Cells(i, "c")) 
   Range("g" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Cells(i, "e"), Cells(i, "c"), Cells(i, "d")) 
   Range("g" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Cells(i, "e"), Cells(i, "d"), Cells(i, "c")) 
Next
add this:
Code:
For i = 252 To Range("c" & Rows.Count).End(xlUp).Row 
   Range("g" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Cells(i, "c"), Cells(i, "d"), Cells(i, "e")) 
   Range("g" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Cells(i, "c"), Cells(i, "e"), Cells(i, "d")) 
   Range("g" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Cells(i, "d"), Cells(i, "c"), Cells(i, "e")) 
   Range("g" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Cells(i, "d"), Cells(i, "e"), Cells(i, "c")) 
   Range("g" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Cells(i, "e"), Cells(i, "c"), Cells(i, "d")) 
   Range("g" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Cells(i, "e"), Cells(i, "d"), Cells(i, "c")) 
Next
and complete with the last part of your origial code
Code:
Range("g5").Resize(, 3).ClearContents 
Application.ScreenUpdating = True 
End Sub
 

Brew

Well-known Member
Joined
Sep 29, 2003
Messages
1,569
I still got my system locked up with this latest update. Im thinking it might be because I only have about 208M of free space available for windows to operate....It's taking must too long to execute.....My PC is about 773Mhz
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
Maybe you can check it out at work, or at a library, or at a friend's house?
 

Brew

Well-known Member
Joined
Sep 29, 2003
Messages
1,569
I entered the updated code as follows, on different machine, however, this machine locks up also. This machine has more memory than the previous one I tested
Sub Conversion()
Dim i As Long
Application.ScreenUpdating = False
Range("g5:i" & Rows.Count).ClearContents
Range("g5").Resize(, 3).Value = Array("h1", "h2", "h3")
For i = 6 To 251 'CHANGE THIS LIMIT TO 251 HERE
Range("g" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Cells(i, "c"), Cells(i, "d"), Cells(i, "e"))
Range("g" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Cells(i, "c"), Cells(i, "e"), Cells(i, "d"))
Range("g" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Cells(i, "d"), Cells(i, "c"), Cells(i, "e"))
Range("g" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Cells(i, "d"), Cells(i, "e"), Cells(i, "c"))
Range("g" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Cells(i, "e"), Cells(i, "c"), Cells(i, "d"))
Range("g" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Cells(i, "e"), Cells(i, "d"), Cells(i, "c"))
Next
For i = 252 To Range("c" & Rows.Count).End(xlUp).Row
Range("g" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Cells(i, "c"), Cells(i, "d"), Cells(i, "e"))
Range("g" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Cells(i, "c"), Cells(i, "e"), Cells(i, "d"))
Range("g" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Cells(i, "d"), Cells(i, "c"), Cells(i, "e"))
Range("g" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Cells(i, "d"), Cells(i, "e"), Cells(i, "c"))
Range("g" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Cells(i, "e"), Cells(i, "c"), Cells(i, "d"))
Range("g" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Cells(i, "e"), Cells(i, "d"), Cells(i, "c"))
Next
Range("g5").Resize(, 3).ClearContents
Application.ScreenUpdating = True
End Sub
 

Forum statistics

Threads
1,141,488
Messages
5,706,665
Members
421,460
Latest member
MTME

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
Top