Macro halt - Why?

[MrRJ]

Good morning,

I have a pretty large macro and when I run it, it comes to a halt, with an
error of non blank cells (it stops on a line where I am inserting a column).
I had to stop the code, then I manually tried to insert a column, and it
didn't work.

NOW, after a several minutes later, I tried to insert the column and IT
WORKS! Why is that? It makes no sense to me. Is it working behind the
scenes?

I hear a lot about screenupdating and calculation turning off. Would that
help? I do not have them in my code. If you think I need it, where should I
put them?

Thanks.

 

[Patrick Molloy]

without seeing the code, its hard to say what the issue is.

As you are inserting a column...it could be that you already have as many
columns as Excel allows?

Patrick

 

[MrRJ]

Hello Patrick,
That would be an easy answer, if I had already too many columns. But, that
is not the case. Since it is part of a larger code, I will paste part of the
code where it stopped. Note, after the code is stopped and several minutes
later, I was able to insert a column, why? Is it doing something behind the
scenes.

Private Sub FORMAT()
' This macro formats the file to be used to upload to CCDS

Columns("BA:BA").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Delete shift:=xlToLeft
Range("A1").Select

Columns("P:Q").Select
Selection.Delete shift:=xlToLeft

Columns("D").Select 'new change
Selection.Copy
Columns("AO:AO").Select
ActiveSheet.Paste
Range("A1").Select

Range("A1").Select
Columns("I:I").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("A:H").Select
Application.CutCopyMode = False
Selection.Delete shift:=xlToLeft

Columns("A:A").Select
Selection.Insert shift:=xlToRight <=======here is where it stopped.
Columns("F:F").Select
Selection.Cut
Columns("B:B").Select
Selection.Insert shift:=xlToRight

 

[MrRJ]

Patrick,
I wish that was the answer. Here is part of my code where it stopped at.
Please note, that after I stopped the code and several minutes later, I was
able to insert the column. WHY?

Private Sub FORMAT()
' This macro formats the file to be used to upload to CCDS

Columns("BA:BA").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Delete shift:=xlToLeft
Range("A1").Select

Columns("P:Q").Select
Selection.Delete shift:=xlToLeft

Columns("D").Select 'new change
Selection.Copy
Columns("AO:AO").Select
ActiveSheet.Paste
Range("A1").Select

Range("A1").Select
Columns("I:I").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("A:H").Select
Application.CutCopyMode = False
Selection.Delete shift:=xlToLeft

Columns("A:A").Select
Selection.Insert shift:=xlToRight <========here is where it stopped.
Columns("F:F").Select
Selection.Cut
Columns("B:B").Select
Selection.Insert shift:=xlToRight

 

[Patrick Molloy]

hmmm I ran the code x1000 in a loop, and it worked fine.

so try
Application.ScreenUpdating=False

at the start of the code and

Application.ScreenUpdating=True

at the and and also switch calculation mode to manual.

 

[MrRJ]

Thanks Patrick,
I will insert the screen updating piece, as I have thought of that. I am
not familiar with calculation mode. Can you provide a sample of the code I
should use?

 

[MrRJ]

Patrick,
I did the following and it still created the problem. Not sure what to do.

Private Sub FORMAT()
' This macro formats the file to be used to upload to CCDS

Application.ScreenUpdating = False
Application.CalculateBeforeSave = False

Columns("BA:BA").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Delete shift:=xlToLeft
Range("A1").Select

Columns("P:Q").Select
Selection.Delete shift:=xlToLeft

Columns("D").Select 'new change
Selection.Copy
Columns("AO:AO").Select
ActiveSheet.Paste
Range("A1").Select

Range("A1").Select
Columns("I:I").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("A:H").Select
Application.CutCopyMode = False
Selection.Delete shift:=xlToLeft
Columns("A:A").Select
Selection.Insert shift:=xlToRight <==========
Columns("F:F").Select
Selection.Cut
Columns("B:B").Select
Selection.Insert shift:=xlToRight

 

[MrRJ]

Patrick,
I have inserted screenupdating and calculationmanual to the code and it
still does not work. Please help? Can I send the code to you via email?

 

[Patrick Molloy]

sure

(e-mail address removed)

Patrick,
I have inserted screenupdating and calculationmanual to the code and it
still does not work. Please help? Can I send the code to you via email?

 

[Rick Rothstein]

While this does not address your question directly, I want to point out that
you can simplify some of your code dramatically. For example, these four
lines of your code (which copies the content of Column D to Column AO)...

Columns("D").Select 'new change
Selection.Copy
Columns("AO:AO").Select
ActiveSheet.Paste

can be replaced with this single line of code...

Columns("D").Copy Range("AO1")

This code fragment is unclear to me, but it appears to delete any formulas
in Column I and replace them with the values the formulas evaluated to...

Columns("I:I").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

If my interpretation is correct, then these four lines can be replaced by
this single line...

Columns("I").Value = Columns("I").Value

The main point of my post is that you do not have to select cells or ranges
of cells in order to work with them. Whenever you see code constructed like
this...

Range("A1").Select
Selection.<whatever>

you can almost always do this instead...

Range("A1").<whatever>

Notice, all I have done is replace Selection with the range you Select(ed)
in the previous statement and eliminate the process of doing any
Select(ion)s. Stated another way, the Selection produced from
Range(...).Select is a range and, of course, Range(...) is a range... and,
in fact, they are the same range, so it doesn't matter which one you use.
The added benefit of not selecting ranges first is your active cell does not
change.

 

[MrRJ]

Hi Patrick,

I am following up on the email that I sent you. Have you any luck?

Thanks,