"run-time error "-2147417848

[Markwpmi]

I am running excel 2010 and getting errors after the first or second times through the code.

Sheets("0Games2").Range("O9:O" & xLastRow).RemoveDuplicates Columns:=1, Header:=xlYes

and then

Sheets("0Games2").Range("O9", Sheets("0Games2").Range("O9").End(xlDown)).Sort Key1:=Sheets("0Games2").Range("O9"), Order1:=xlAscending, Header:=xlYes
and I also tried adding this prior to this code

xLastRow = Sheets("0Games2").Range("O1048576").End(xlUp).Row

Sheets("0Games2").Sort.SortFields.Clear

the .Clear did not help

I have a worksheet that stores data form 118 sheets of 5 columns of data where I use VBA code to manually filter the 5 columns then create 4 more columns that have no duplicates and sorted using the code above. I had all of this working with out VBA code for the selection but it was getting very slow and I need to add more sheets that would expand this to maybe 10000 from the current 128

I feel that the memory is full???

[#2]

what error message do you get?

maybe replace xLastRow = Sheets("0Games2").Range("O1048576").End(xlUp).Row

with xLastRow = Sheets("0Games2").Cells(Rows.Count, 15).End(xlUp).Row

I don't think this will solve it but it might improve performance

[#3 · Accepted solution]

I have found the problem. One of the issues is that the "Private Sub Worksheet_Change(ByVal Target As Range)" runs wild if you are changing cells on the page.  This took me many days to figure it out.

1st the control code I have added a NoReRun Booolean as a Global 

Then in the I added this change. The upper area is not required code

Then since the Boolean is automatically set to False this works well and to prevent the re-running of all the code over and over again. This allows the code to run but just passes the If Then End If without running the code. Use something like this code. As you see  I am changing Range("B3:T3") which caused the code to loop over and over and over ... again, causing wild run and/or Errors. By Setting NoReRun = True solves the issue.

I have created a a update to another Sheet as well so if there is a Change code on that Sheet you might have to add code to prevent the over run on that Sheet too, I did not have to do that in my code

@PascalKTeam