| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro halt - Why?
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro halt - Why?
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" wrote: 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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro halt - Why?
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: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" wrote: 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" wrote: 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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro halt - Why?
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: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" wrote: 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" wrote: 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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro halt - Why?
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" wrote: 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: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" wrote: 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" wrote: 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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro halt - Why?
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? "Patrick Molloy" wrote: 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" wrote: 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: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" wrote: 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" wrote: 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. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro halt - Why?
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: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 "Patrick Molloy" wrote: 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" wrote: 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: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" wrote: 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" wrote: 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. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro halt - Why?
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" wrote: 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" wrote: 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: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" wrote: 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" wrote: 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. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro halt - Why?
|
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro halt - Why?
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:D").Select 'new change Selection.Copy Columns("AO:AO").Select ActiveSheet.Paste can be replaced with this single line of code... Columns("D: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. -- Rick (MVP - Excel) "MrRJ" wrote in message ... 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: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 "Patrick Molloy" wrote: 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" wrote: 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: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" wrote: 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" wrote: 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. |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro halt - Why?
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Halt Macro Execution | Excel Programming | |||
| Halt or Pause a Macro | Excel Programming | |||
| Halt A Macro If a Cell Contains a Certain Value | Excel Programming | |||
| Halt all code while macro runs | Excel Programming | |||
| Code to halt a macro | Excel Programming | |||
Linear Mode
