make code shorter as excel gets stuck

[aayaanmayank]

Hi Can anyone help me to make below code shorter as whenever my i run the code along with different set of code, it always gets hang when macro reach to this code.

Sub Groupingname()


Application.ScreenUpdating = False

Set shgroup = ThisWorkbook.Worksheets("Data")




Range("I2").Select
For U = 2 To lastrow1


Set MYNAME0 = Cells((U + 1), ("I"))
Set MYNAME1 = Cells(U, "j")
Set MYNAME2 = Cells(U, "I")


If MYNAME0.Interior.Color = vbYellow Or MYNAME0.Interior.Color = vbGreen And MYNAME0.Value = MYNAME1.Value Then

If IsEmpty(shgroup.Cells((U + 1), ("I"))) = False Then
MYNAME0.Interior.Color = vbGreen
MYNAME1.Interior.Color = vbGreen
MYNAME2.Interior.Color = vbGreen

Else:
End If
End If
Next U

end sub

 

[aayaanmayank]

Re: make code sorter as excel gets stuck

Apologies!

here is the complete code -

Sub Groupingname()


Set shgroup = ThisWorkbook.Worksheets("DATA")
lastrow1 = shgroup.Range("B" & Rows.Count).End(xlUp).Row




Varray = Range(Cells(1, 9), Cells(lastrow1 + 1, 9))
shgroup.Range(Cells(2, 9), Cells(lastrow1, 9)).Interior.Color = vbYellow


For U = 2 To lastrow1
MYNAME2 = Varray(U, 1)
MYNAME3 = Varray((U + 1), 1)
If MYNAME2 = MYNAME3 Then
shgroup.Cells(U, "I").Interior.Color = vbGreen
shgroup.Cells(U + 1, "I").Interior.Color = vbGreen
End If
Next U


Range("I2").Select
For U = 2 To lastrow1


Set MYNAME0 = Cells((U + 1), ("I"))
Set MYNAME1 = Cells(U, "j")
Set MYNAME2 = Cells(U, "I")


If (MYNAME0.Interior.Color = vbYellow Or MYNAME0.Interior.Color = vbGreen) And MYNAME0.Value = MYNAME1.Value Then


If IsEmpty(shgroup.Cells((U + 1), ("I"))) = False Then
MYNAME0.Interior.Color = vbGreen
MYNAME1.Interior.Color = vbGreen
MYNAME2.Interior.Color = vbGreen


Else:
End If
End If
Next U


End Sub

 

[aayaanmayank]

Re: make code sorter as excel gets stuck

it gets hang and i get the error which i got just now while running my macro- Microsoft excel has stopped working

 

[aayaanmayank]

Re: make code sorter as excel gets stuck

Sorry i tried how to add tag but did not succeed. now i understood how to do that.

 

[Yongle]

Re: make code sorter as excel gets stuck

Did you test what I suggested in post#6 ?

 

[Rick Rothstein]

Re: make code sorter as excel gets stuck

Apologies!

here is the complete code -

Sub Groupingname()


Set shgroup = ThisWorkbook.Worksheets("DATA")
lastrow1 = shgroup.Range("B" & Rows.Count).End(xlUp).Row




Varray = Range([B][COLOR="#FF0000"]Cells(1, 9)[/COLOR][/B], [B][COLOR="#FF0000"]Cells(lastrow1 + 1, 9)[/COLOR][/B])
shgroup.Range([B][COLOR="#FF0000"]Cells(2, 9)[/COLOR][/B], [B][COLOR="#FF0000"]Cells(lastrow1, 9)[/COLOR][/B]).Interior.Color = vbYellow


For U = 2 To lastrow1
MYNAME2 = Varray(U, 1)
MYNAME3 = Varray((U + 1), 1)
If MYNAME2 = MYNAME3 Then
shgroup.Cells(U, "I").Interior.Color = vbGreen
shgroup.Cells(U + 1, "I").Interior.Color = vbGreen
End If
Next U


[B][COLOR="#FF0000"]Range("I2")[/COLOR][/B].Select
For U = 2 To lastrow1


Set MYNAME0 = [B][COLOR="#FF0000"]Cells((U + 1), ("I"))[/COLOR][/B]
Set MYNAME1 =[B][COLOR="#FF0000"] Cells(U, "j")[/COLOR][/B]
Set MYNAME2 = [B][COLOR="#FF0000"]Cells(U, "I")[/COLOR][/B]


If (MYNAME0.Interior.Color = vbYellow Or MYNAME0.Interior.Color = vbGreen) And MYNAME0.Value = MYNAME1.Value Then


If IsEmpty(shgroup.Cells((U + 1), ("I"))) = False Then
MYNAME0.Interior.Color = vbGreen
MYNAME1.Interior.Color = vbGreen
MYNAME2.Interior.Color = vbGreen


Else:
End If
End If
Next U


End Sub

I did not study your code to see if it made sense code-wise; however, I thought I should point out that every range that I highlighted in red will reference the ActiveSheet. It is hard to know whether that is intentional or not. But if you are supposed to be referencing the DATA sheet for them, you are not UNLESS you are running the code with the DATA sheet active.

 

[aayaanmayank]

Re: make code sorter as excel gets stuck

Yes it is referring to only one sheet, i am not activating AS DATA sheet because there is no second sheet. and my concern is not that my code is not doing my job. it is doing the way i want but when i run data with 30k rows then most of the time my excel gets hang and throws error that "excel stop working".

 

[offthelip]

Re: make code sorter as excel gets stuck

When designing your EXCEL system you should understand the strengths and limitations of EXCEL. One of the limitations is accessing the worksheet from VBA is very slow and uses a lot of resources. SO formating each one of 30K cells individually is always going to be very slow and is quite likely to fall over and crash due to lack of resources. I suspect this is what is happening in your case. What is a strength of EXCCEL and VBA is that the time taken to access an entire range ( almost of any size) is almost the same as the time taken to access one cell. So the way I would approach your problems is to give up trying to format each cell individually but instead calculate a boolean flag in a variant array and write the variant arrray out to a spare column on the spreadsheet. Then i would try using conditional formatting on the whole column to colour the cells yellwo or green depending on the flag in the additional column. This might will slow down the recalculation of the worksheet too much but it is worth a try.

 

[aayaanmayank]

Re: make code sorter as excel gets stuck

Hi Yongle, yes i tried, but i am sorry your code is not solving my purpose.

 

[aayaanmayank]

Re: make code sorter as excel gets stuck

My m using 64 BIT.