Error "Microsoft office excel cannot create or use the data range reference because it is too complex"

[pedie]

Hi 'm getting this error message "Microsoft office excel cannot create or use the data range reference because it is too complex" when i try to delete/copy/cut/select chunk of data...

Can someone please help me with this get rid of this error messange and work this out?

I have data of more then 557000 row....4 cols...

Please advice.

 

[MrKowz]

Are you trying to edit a large amount of data at once? If so, try breaking it off into chunks. Editing ~2 million cells at once is a lot.

 

[pedie]

Hi thanks for answering....

breaking data takes around 3mins...filtering errors...and for loop takes almost 6mins....
So pls help!

 

[kpark91]

Could you post your code?

Maybe some optimization can be done to it.

 

[pedie]

One of the code:

[/FONT]
[FONT=Courier New]Option Explicit
Sub loop4()
Dim lr As Long
Dim i As Range
lr = Range("A" & Rows.Count).End(xlUp).Row
 For Each i In Range("A1:A" & lr)
  If i.Value Like "MyIndex No:" & "*" Or i.Value Like "Data Xi No:" & "*" Then
   i.Copy Range("F" & Rows.Count).End(xlUp).Offset(1, 0)
  End If
 Next i
End Sub

 

[MrKowz]

Try:

Sub loop4()
Dim lr As Long
Dim i As Range
Dim tmp As String
lr = Range("A" & Rows.Count).End(xlUp).Row
 For Each i In Range("A1:A" & lr)
  tmp = Left$(i.Value, InStr(i.Value, ":"))
  If tmp = "MyIndex No:" Or tmp = "Data Xi No:" Then
   i.Copy Destination:=Range("F" & Rows.Count).End(xlUp).Offset(1, 0)
  End If
 Next i
End Sub

 

[pedie]

Thanks alot but it still does takes around 5.23 mins...

 

[pedie]

How can i break down data?

Can you pls write a code to divide data into 4 different part from count of A and then cut and paste it in different sheet organise and bring required data back to same old sheet?

Thanks again

 

[MrKowz]

Thanks alot but it still does takes around 5.23 mins...

That's because you didn't turn off calculations and screenupdating. This should considerably speed it up.

Sub loop4()
Dim lr As Long
Dim i As Range
Dim tmp As String
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    .EnableEvents = False
End With
lr = Range("A" & Rows.Count).End(xlUp).Row
For Each i In Range("A1:A" & lr)
    tmp = Left$(i.Value, InStr(i.Value, ":"))
    If tmp = "MyIndex No:" Or tmp = "Data Xi No:" Then
        i.Copy Destination:=Range("F" & Rows.Count).End(xlUp).Offset(1, 0)
    End If
Next i
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
End With
End Sub

 

[pedie]

Thanks...thats much better MrKows! I did turn off screen updating but didnt take care of calculation part...

Can you also please help me with my post#8?