|
Application.Screenupdating not "always" working?
Hi
A part of my macro clears the contents of a part of a row, selects a range below it, cuts the range, and then pastes it to the row previously cleared, and then repeats until all rows in the range are cleared. This is the only part of the macro not "hidden" by Application.Screenupdating = false, so the user will see all the activity. Is there a way to overcome this? Thanks in advance Joe |
Application.Screenupdating not "always" working?
If you are using Select, that may be giving you trouble. One does not need
to use the Select method to act on Excel objects. You can also hide the sheet, the workbook, the application window in VBA while processing. I understand that you may not want to hide the entire app.window. Application.Screen Updating usually works as advertised. I bet there is a way to overcome this. How about posting a little code that illustrates the problem? -- Bob Kilmer "Joe 90" wrote in message ... Hi A part of my macro clears the contents of a part of a row, selects a range below it, cuts the range, and then pastes it to the row previously cleared, and then repeats until all rows in the range are cleared. This is the only part of the macro not "hidden" by Application.Screenupdating = false, so the user will see all the activity. Is there a way to overcome this? Thanks in advance Joe |
Application.Screenupdating not "always" working?
Bob,
I can see why Select is a problem :) but have found that if I want the current region selected, I have to use Select ?? Will post up some code to help with the problem. Joe "Bob Kilmer" wrote in message ... If you are using Select, that may be giving you trouble. One does not need to use the Select method to act on Excel objects. You can also hide the sheet, the workbook, the application window in VBA while processing. I understand that you may not want to hide the entire app.window. Application.Screen Updating usually works as advertised. I bet there is a way to overcome this. How about posting a little code that illustrates the problem? -- Bob Kilmer "Joe 90" wrote in message ... Hi A part of my macro clears the contents of a part of a row, selects a range below it, cuts the range, and then pastes it to the row previously cleared, and then repeats until all rows in the range are cleared. This is the only part of the macro not "hidden" by Application.Screenupdating = false, so the user will see all the activity. Is there a way to overcome this? Thanks in advance Joe |
Application.Screenupdating not "always" working?
Joe,
I cannot make Select violate ScreenUpdating = False. Didn't think it should. Not sure what is causing your trouble. I'll look at your code when you post it, unless you figure it out first. <g As an aside, I use Select if I want the interface to show a selection, but generally, using selecting in code is not necessary. Simply refering to the objects creates faster, more concise code. For example the following code was recorded: Range("A1").Select Selection.Copy Range("B2").Select ActiveSheet.Paste This is equivalent: Range("A1").Copy Range("B2") -- Regards, Bob Kilmer "Joe 90" wrote in message ... Bob, I can see why Select is a problem :) but have found that if I want the current region selected, I have to use Select ?? Will post up some code to help with the problem. Joe "Bob Kilmer" wrote in message ... If you are using Select, that may be giving you trouble. One does not need to use the Select method to act on Excel objects. You can also hide the sheet, the workbook, the application window in VBA while processing. I understand that you may not want to hide the entire app.window. Application.Screen Updating usually works as advertised. I bet there is a way to overcome this. How about posting a little code that illustrates the problem? -- Bob Kilmer "Joe 90" wrote in message ... Hi A part of my macro clears the contents of a part of a row, selects a range below it, cuts the range, and then pastes it to the row previously cleared, and then repeats until all rows in the range are cleared. This is the only part of the macro not "hidden" by Application.Screenupdating = false, so the user will see all the activity. Is there a way to overcome this? Thanks in advance Joe |
Application.Screenupdating not "always" working?
Bob
So can I "select" (in inverted commas!) a CurrentRegion without using select? (Hope that makes sense :) ) Joe "Bob Kilmer" wrote in message ... Joe, I cannot make Select violate ScreenUpdating = False. Didn't think it should. Not sure what is causing your trouble. I'll look at your code when you post it, unless you figure it out first. <g As an aside, I use Select if I want the interface to show a selection, but generally, using selecting in code is not necessary. Simply refering to the objects creates faster, more concise code. For example the following code was recorded: Range("A1").Select Selection.Copy Range("B2").Select ActiveSheet.Paste This is equivalent: Range("A1").Copy Range("B2") -- Regards, Bob Kilmer "Joe 90" wrote in message ... Bob, I can see why Select is a problem :) but have found that if I want the current region selected, I have to use Select ?? Will post up some code to help with the problem. Joe "Bob Kilmer" wrote in message ... If you are using Select, that may be giving you trouble. One does not need to use the Select method to act on Excel objects. You can also hide the sheet, the workbook, the application window in VBA while processing. I understand that you may not want to hide the entire app.window. Application.Screen Updating usually works as advertised. I bet there is a way to overcome this. How about posting a little code that illustrates the problem? -- Bob Kilmer "Joe 90" wrote in message ... Hi A part of my macro clears the contents of a part of a row, selects a range below it, cuts the range, and then pastes it to the row previously cleared, and then repeats until all rows in the range are cleared. This is the only part of the macro not "hidden" by Application.Screenupdating = false, so the user will see all the activity. Is there a way to overcome this? Thanks in advance Joe |
Application.Screenupdating not "always" working?
Well, yes, in a sense.
You can Copy a Range. A CurrentRegion is a Range. Therefore, you can Copy a CurrentRegion. You can Select a Range. A CurrentRegion is a Range. Therefore, you can Select a CurrentRegion. A Selection is a Range. Therefore, you can Copy a Selection. It is no sin to use Select, but it is usually neater, more concise and often faster. If you want to copy the range, you may as well copy the range instead of selecting the range then copying the selection. If you want to run a macro, say, that operates on the user selection, that is a good time to use the Selection range. If you want to *show* a range of cells as having been selected, use Select. -- Bob Kilmer "Joe 90" wrote in message ... Bob So can I "select" (in inverted commas!) a CurrentRegion without using select? (Hope that makes sense :) ) Joe "Bob Kilmer" wrote in message ... Joe, I cannot make Select violate ScreenUpdating = False. Didn't think it should. Not sure what is causing your trouble. I'll look at your code when you post it, unless you figure it out first. <g As an aside, I use Select if I want the interface to show a selection, but generally, using selecting in code is not necessary. Simply refering to the objects creates faster, more concise code. For example the following code was recorded: Range("A1").Select Selection.Copy Range("B2").Select ActiveSheet.Paste This is equivalent: Range("A1").Copy Range("B2") -- Regards, Bob Kilmer "Joe 90" wrote in message ... Bob, I can see why Select is a problem :) but have found that if I want the current region selected, I have to use Select ?? Will post up some code to help with the problem. Joe "Bob Kilmer" wrote in message ... If you are using Select, that may be giving you trouble. One does not need to use the Select method to act on Excel objects. You can also hide the sheet, the workbook, the application window in VBA while processing. I understand that you may not want to hide the entire app.window. Application.Screen Updating usually works as advertised. I bet there is a way to overcome this. How about posting a little code that illustrates the problem? -- Bob Kilmer "Joe 90" wrote in message ... Hi A part of my macro clears the contents of a part of a row, selects a range below it, cuts the range, and then pastes it to the row previously cleared, and then repeats until all rows in the range are cleared. This is the only part of the macro not "hidden" by Application.Screenupdating = false, so the user will see all the activity. Is there a way to overcome this? Thanks in advance Joe |
Application.Screenupdating not "always" working?
Bob, My code calls all over the place to several procedures, so I have decided to go through it line by line, seeking to remove "select" where I can. When I find the culprit I'll let you know :) The only trouble being is that when F8'ing through the code Application Screen Updating doesn't work at all lol Joe "Bob Kilmer" wrote in message ... Well, yes, in a sense. You can Copy a Range. A CurrentRegion is a Range. Therefore, you can Copy a CurrentRegion. You can Select a Range. A CurrentRegion is a Range. Therefore, you can Select a CurrentRegion. A Selection is a Range. Therefore, you can Copy a Selection. It is no sin to use Select, but it is usually neater, more concise and often faster. If you want to copy the range, you may as well copy the range instead of selecting the range then copying the selection. If you want to run a macro, say, that operates on the user selection, that is a good time to use the Selection range. If you want to *show* a range of cells as having been selected, use Select. -- Bob Kilmer "Joe 90" wrote in message ... Bob So can I "select" (in inverted commas!) a CurrentRegion without using select? (Hope that makes sense :) ) Joe "Bob Kilmer" wrote in message ... Joe, I cannot make Select violate ScreenUpdating = False. Didn't think it should. Not sure what is causing your trouble. I'll look at your code when you post it, unless you figure it out first. <g As an aside, I use Select if I want the interface to show a selection, but generally, using selecting in code is not necessary. Simply refering to the objects creates faster, more concise code. For example the following code was recorded: Range("A1").Select Selection.Copy Range("B2").Select ActiveSheet.Paste This is equivalent: Range("A1").Copy Range("B2") -- Regards, Bob Kilmer "Joe 90" wrote in message ... Bob, I can see why Select is a problem :) but have found that if I want the current region selected, I have to use Select ?? Will post up some code to help with the problem. Joe "Bob Kilmer" wrote in message ... If you are using Select, that may be giving you trouble. One does not need to use the Select method to act on Excel objects. You can also hide the sheet, the workbook, the application window in VBA while processing. I understand that you may not want to hide the entire app.window. Application.Screen Updating usually works as advertised. I bet there is a way to overcome this. How about posting a little code that illustrates the problem? -- Bob Kilmer "Joe 90" wrote in message ... Hi A part of my macro clears the contents of a part of a row, selects a range below it, cuts the range, and then pastes it to the row previously cleared, and then repeats until all rows in the range are cleared. This is the only part of the macro not "hidden" by Application.Screenupdating = false, so the user will see all the activity. Is there a way to overcome this? Thanks in advance Joe |
Application.Screenupdating not "always" working?
Bob,
Not using 'select' is a personal goal of mine at the moment I use the copy argument like yourself detailed below - can this be used with a pastespecial without selecting anything? J "Bob Kilmer" wrote in message ... Joe, I cannot make Select violate ScreenUpdating = False. Didn't think it should. Not sure what is causing your trouble. I'll look at your code when you post it, unless you figure it out first. <g As an aside, I use Select if I want the interface to show a selection, but generally, using selecting in code is not necessary. Simply refering to the objects creates faster, more concise code. For example the following code was recorded: Range("A1").Select Selection.Copy Range("B2").Select ActiveSheet.Paste This is equivalent: Range("A1").Copy Range("B2") -- Regards, Bob Kilmer "Joe 90" wrote in message ... Bob, I can see why Select is a problem :) but have found that if I want the current region selected, I have to use Select ?? Will post up some code to help with the problem. Joe "Bob Kilmer" wrote in message ... If you are using Select, that may be giving you trouble. One does not need to use the Select method to act on Excel objects. You can also hide the sheet, the workbook, the application window in VBA while processing. I understand that you may not want to hide the entire app.window. Application.Screen Updating usually works as advertised. I bet there is a way to overcome this. How about posting a little code that illustrates the problem? -- Bob Kilmer "Joe 90" wrote in message ... Hi A part of my macro clears the contents of a part of a row, selects a range below it, cuts the range, and then pastes it to the row previously cleared, and then repeats until all rows in the range are cleared. This is the only part of the macro not "hidden" by Application.Screenupdating = false, so the user will see all the activity. Is there a way to overcome this? Thanks in advance Joe |
Application.Screenupdating not "always" working?
Bob
Found my first problem: [code] Worksheets("Sheet1").Select (or Activate) [end code] causes a breakout from Application.ScreenUpdating=false So I am trying to reference the range I need using: [code] Worksheets("Sheet1").Range("J2", Range("J2").End(xlDown)). Copy Worksheets("Sheet2").Range("b2") [end code] but I get an object error. I need to reference the range with .End because the size of range starting at J2 will keep changing. All/any help greatly appreciated Joe "Bob Kilmer" wrote in message ... Well, yes, in a sense. You can Copy a Range. A CurrentRegion is a Range. Therefore, you can Copy a CurrentRegion. You can Select a Range. A CurrentRegion is a Range. Therefore, you can Select a CurrentRegion. A Selection is a Range. Therefore, you can Copy a Selection. It is no sin to use Select, but it is usually neater, more concise and often faster. If you want to copy the range, you may as well copy the range instead of selecting the range then copying the selection. If you want to run a macro, say, that operates on the user selection, that is a good time to use the Selection range. If you want to *show* a range of cells as having been selected, use Select. -- Bob Kilmer "Joe 90" wrote in message ... Bob So can I "select" (in inverted commas!) a CurrentRegion without using select? (Hope that makes sense :) ) Joe "Bob Kilmer" wrote in message ... Joe, I cannot make Select violate ScreenUpdating = False. Didn't think it should. Not sure what is causing your trouble. I'll look at your code when you post it, unless you figure it out first. <g As an aside, I use Select if I want the interface to show a selection, but generally, using selecting in code is not necessary. Simply refering to the objects creates faster, more concise code. For example the following code was recorded: Range("A1").Select Selection.Copy Range("B2").Select ActiveSheet.Paste This is equivalent: Range("A1").Copy Range("B2") -- Regards, Bob Kilmer "Joe 90" wrote in message ... Bob, I can see why Select is a problem :) but have found that if I want the current region selected, I have to use Select ?? Will post up some code to help with the problem. Joe "Bob Kilmer" wrote in message ... If you are using Select, that may be giving you trouble. One does not need to use the Select method to act on Excel objects. You can also hide the sheet, the workbook, the application window in VBA while processing. I understand that you may not want to hide the entire app.window. Application.Screen Updating usually works as advertised. I bet there is a way to overcome this. How about posting a little code that illustrates the problem? -- Bob Kilmer "Joe 90" wrote in message ... Hi A part of my macro clears the contents of a part of a row, selects a range below it, cuts the range, and then pastes it to the row previously cleared, and then repeats until all rows in the range are cleared. This is the only part of the macro not "hidden" by Application.Screenupdating = false, so the user will see all the activity. Is there a way to overcome this? Thanks in advance Joe |
Application.Screenupdating not "always" working?
Joe 90 wrote:
Bob Found my first problem: Code: -------------------- Worksheets("Sheet1").Select (or Activate) [end code] causes a breakout from Application.ScreenUpdating=false So I am trying to reference the range I need using: Code: -------------------- Worksheets("Sheet1").Range("J2", Range("J2").End(xlDown)). Copy Worksheets("Sheet2").Range("b2") [end code] but I get an object error. I need to reference the range with .End because the size of range starting at J2 will keep changing. All/any help greatly appreciated Joe Should it be this? Code: -------------------- Worksheets("Sheet1").Range("J2"*:* Range("J2").End(xlDown)). Copy Worksheets("Sheet2").Range("b2") [end code] I think you need to have a colon to help select the range, not the comma that was there. I hope that works... I could sure use a formula that select ranges like that. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
| All times are GMT +1. The time now is 04:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com