|
Macro limitations
I have a 9 mb file that I want to create a macro that will copy/paste/new
workbook/creat a copy for one of the sheets, then copy/paste values some of the cells in the new workbook, then delete A1:BB1119. The macro I have written should work, but does not. If I delete the rows and then the columns, the rows will delete, but not the columns. If I delete the columns first, the rows will not delete. I am thinking Excel may have a limit on how many deletions it can do in a macro. Do you have any ideas on how I can accomplish this task? -- Mary Frances |
Macro limitations
Excel is making sure you don't incorrectly delete additional information you
still need. If excel did what youi want then data at higher rows above 1119 would also get deleted and data to the right of cell BB would get delete. These two additional areas would get deleted. A1120:BB65535 BC1:IV1119 "Mary Frances" wrote: I have a 9 mb file that I want to create a macro that will copy/paste/new workbook/creat a copy for one of the sheets, then copy/paste values some of the cells in the new workbook, then delete A1:BB1119. The macro I have written should work, but does not. If I delete the rows and then the columns, the rows will delete, but not the columns. If I delete the columns first, the rows will not delete. I am thinking Excel may have a limit on how many deletions it can do in a macro. Do you have any ideas on how I can accomplish this task? -- Mary Frances |
Macro limitations
[code]
Sheets("Analyzer").Copy Application.Goto Reference:="R1120C54" Range("BB1120:BV1139").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("BC1140:BI1168").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWindow.SmallScroll Down:=31 Range("BC1178:BI1181").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWindow.SmallScroll Down:=-50 Range("BB1:BB1118").Select Range("BB1118").Activate Application.CutCopyMode = False Selection.EntireRow.Delete Range("A1:BB1").Select Range("BB1").Activate Application.CutCopyMode = False Selection.EntireColumn.Delete Range("A1").Select -- Mary Frances "Don Guillett" wrote: Yes, Post your code. -- Don Guillett Microsoft MVP Excel SalesAid Software "Mary Frances" wrote in message ... I have a 9 mb file that I want to create a macro that will copy/paste/new workbook/creat a copy for one of the sheets, then copy/paste values some of the cells in the new workbook, then delete A1:BB1119. The macro I have written should work, but does not. If I delete the rows and then the columns, the rows will delete, but not the columns. If I delete the columns first, the rows will not delete. I am thinking Excel may have a limit on how many deletions it can do in a macro. Do you have any ideas on how I can accomplish this task? -- Mary Frances |
Macro limitations
========== Sheets("Analyzer").Copy Application.Goto Reference:="R1120C54" ===========don't understand above that does nothing Range("BB1120:BV1139").value=Range("BB1120:BV1139" ).value Range("BC1140:BI1168").value=Range("BC1140:BI1168" ).value Range("BC1178:BI1181").value=Range("BC1178:BI1181" ).value Range("BB1:BB1118").EntireRow.Delete Range("A1:BB1").EntireColumn.Delete could even be shortened to Range("BB1120:BV1139,bc1140:bi1168,bc1178:bi1181") .value= _ Range("BB1120:BV1139,bc1140:bi1168,bc1178:bi1181") .value rows("1:1118").delete Range("A1:BB1").EntireColumn.Delete -- Don Guillett Microsoft MVP Excel SalesAid Software "Mary Frances" wrote in message ... [code] Sheets("Analyzer").Copy Application.Goto Reference:="R1120C54" Range("BB1120:BV1139").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("BC1140:BI1168").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWindow.SmallScroll Down:=31 Range("BC1178:BI1181").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWindow.SmallScroll Down:=-50 Range("BB1:BB1118").Select Range("BB1118").Activate Application.CutCopyMode = False Selection.EntireRow.Delete Range("A1:BB1").Select Range("BB1").Activate Application.CutCopyMode = False Selection.EntireColumn.Delete Range("A1").Select -- Mary Frances "Don Guillett" wrote: Yes, Post your code. -- Don Guillett Microsoft MVP Excel SalesAid Software "Mary Frances" wrote in message ... I have a 9 mb file that I want to create a macro that will copy/paste/new workbook/creat a copy for one of the sheets, then copy/paste values some of the cells in the new workbook, then delete A1:BB1119. The macro I have written should work, but does not. If I delete the rows and then the columns, the rows will delete, but not the columns. If I delete the columns first, the rows will not delete. I am thinking Excel may have a limit on how many deletions it can do in a macro. Do you have any ideas on how I can accomplish this task? -- Mary Frances |
Macro limitations
I entered the code as you have it, but it still does not delete the columns.
The rows are gone, but the columns are still there. Any ideas on why this is happening? -- Mary Frances "Don Guillett" wrote: ========== Sheets("Analyzer").Copy Application.Goto Reference:="R1120C54" ===========don't understand above that does nothing Range("BB1120:BV1139").value=Range("BB1120:BV1139" ).value Range("BC1140:BI1168").value=Range("BC1140:BI1168" ).value Range("BC1178:BI1181").value=Range("BC1178:BI1181" ).value Range("BB1:BB1118").EntireRow.Delete Range("A1:BB1").EntireColumn.Delete could even be shortened to Range("BB1120:BV1139,bc1140:bi1168,bc1178:bi1181") .value= _ Range("BB1120:BV1139,bc1140:bi1168,bc1178:bi1181") .value rows("1:1118").delete Range("A1:BB1").EntireColumn.Delete -- Don Guillett Microsoft MVP Excel SalesAid Software "Mary Frances" wrote in message ... [code] Sheets("Analyzer").Copy Application.Goto Reference:="R1120C54" Range("BB1120:BV1139").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("BC1140:BI1168").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWindow.SmallScroll Down:=31 Range("BC1178:BI1181").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWindow.SmallScroll Down:=-50 Range("BB1:BB1118").Select Range("BB1118").Activate Application.CutCopyMode = False Selection.EntireRow.Delete Range("A1:BB1").Select Range("BB1").Activate Application.CutCopyMode = False Selection.EntireColumn.Delete Range("A1").Select -- Mary Frances "Don Guillett" wrote: Yes, Post your code. -- Don Guillett Microsoft MVP Excel SalesAid Software "Mary Frances" wrote in message ... I have a 9 mb file that I want to create a macro that will copy/paste/new workbook/creat a copy for one of the sheets, then copy/paste values some of the cells in the new workbook, then delete A1:BB1119. The macro I have written should work, but does not. If I delete the rows and then the columns, the rows will delete, but not the columns. If I delete the columns first, the rows will not delete. I am thinking Excel may have a limit on how many deletions it can do in a macro. Do you have any ideas on how I can accomplish this task? -- Mary Frances |
Macro limitations
Send to my address below and I'll have a look.
-- Don Guillett Microsoft MVP Excel SalesAid Software "Mary Frances" wrote in message ... I entered the code as you have it, but it still does not delete the columns. The rows are gone, but the columns are still there. Any ideas on why this is happening? -- Mary Frances "Don Guillett" wrote: ========== Sheets("Analyzer").Copy Application.Goto Reference:="R1120C54" ===========don't understand above that does nothing Range("BB1120:BV1139").value=Range("BB1120:BV1139" ).value Range("BC1140:BI1168").value=Range("BC1140:BI1168" ).value Range("BC1178:BI1181").value=Range("BC1178:BI1181" ).value Range("BB1:BB1118").EntireRow.Delete Range("A1:BB1").EntireColumn.Delete could even be shortened to Range("BB1120:BV1139,bc1140:bi1168,bc1178:bi1181") .value= _ Range("BB1120:BV1139,bc1140:bi1168,bc1178:bi1181") .value rows("1:1118").delete Range("A1:BB1").EntireColumn.Delete -- Don Guillett Microsoft MVP Excel SalesAid Software "Mary Frances" wrote in message ... [code] Sheets("Analyzer").Copy Application.Goto Reference:="R1120C54" Range("BB1120:BV1139").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("BC1140:BI1168").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWindow.SmallScroll Down:=31 Range("BC1178:BI1181").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWindow.SmallScroll Down:=-50 Range("BB1:BB1118").Select Range("BB1118").Activate Application.CutCopyMode = False Selection.EntireRow.Delete Range("A1:BB1").Select Range("BB1").Activate Application.CutCopyMode = False Selection.EntireColumn.Delete Range("A1").Select -- Mary Frances "Don Guillett" wrote: Yes, Post your code. -- Don Guillett Microsoft MVP Excel SalesAid Software "Mary Frances" wrote in message ... I have a 9 mb file that I want to create a macro that will copy/paste/new workbook/creat a copy for one of the sheets, then copy/paste values some of the cells in the new workbook, then delete A1:BB1119. The macro I have written should work, but does not. If I delete the rows and then the columns, the rows will delete, but not the columns. If I delete the columns first, the rows will not delete. I am thinking Excel may have a limit on how many deletions it can do in a macro. Do you have any ideas on how I can accomplish this task? -- Mary Frances |
Macro limitations
In preparing the file to send to you (there is some very sensitive
information contained in the workbook that had to be deleted), I found that the macro would work with all of the formulas deleted. When I tried it again on the full version, the columns would still not delete. The one sheet is 1.05 mb; could that be the problem? Maybe there is too much information for Excel to delete in one macro. -- Mary Frances "Don Guillett" wrote: Send to my address below and I'll have a look. -- Don Guillett Microsoft MVP Excel SalesAid Software "Mary Frances" wrote in message ... I entered the code as you have it, but it still does not delete the columns. The rows are gone, but the columns are still there. Any ideas on why this is happening? -- Mary Frances "Don Guillett" wrote: ========== Sheets("Analyzer").Copy Application.Goto Reference:="R1120C54" ===========don't understand above that does nothing Range("BB1120:BV1139").value=Range("BB1120:BV1139" ).value Range("BC1140:BI1168").value=Range("BC1140:BI1168" ).value Range("BC1178:BI1181").value=Range("BC1178:BI1181" ).value Range("BB1:BB1118").EntireRow.Delete Range("A1:BB1").EntireColumn.Delete could even be shortened to Range("BB1120:BV1139,bc1140:bi1168,bc1178:bi1181") .value= _ Range("BB1120:BV1139,bc1140:bi1168,bc1178:bi1181") .value rows("1:1118").delete Range("A1:BB1").EntireColumn.Delete -- Don Guillett Microsoft MVP Excel SalesAid Software "Mary Frances" wrote in message ... [code] Sheets("Analyzer").Copy Application.Goto Reference:="R1120C54" Range("BB1120:BV1139").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("BC1140:BI1168").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWindow.SmallScroll Down:=31 Range("BC1178:BI1181").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWindow.SmallScroll Down:=-50 Range("BB1:BB1118").Select Range("BB1118").Activate Application.CutCopyMode = False Selection.EntireRow.Delete Range("A1:BB1").Select Range("BB1").Activate Application.CutCopyMode = False Selection.EntireColumn.Delete Range("A1").Select -- Mary Frances "Don Guillett" wrote: Yes, Post your code. -- Don Guillett Microsoft MVP Excel SalesAid Software "Mary Frances" wrote in message ... I have a 9 mb file that I want to create a macro that will copy/paste/new workbook/creat a copy for one of the sheets, then copy/paste values some of the cells in the new workbook, then delete A1:BB1119. The macro I have written should work, but does not. If I delete the rows and then the columns, the rows will delete, but not the columns. If I delete the columns first, the rows will not delete. I am thinking Excel may have a limit on how many deletions it can do in a macro. Do you have any ideas on how I can accomplish this task? -- Mary Frances |
Macro limitations
Why would rows to the right of cell BB get deleted? Did you mean to the left?
There is no information in A1:BB1119 that I need so I am trying to delete that area. Is there a limit to how much can be deleted in a macro? The sheet is a 1.9 mb file when removed from the workbook. When I delete all of the information on the sheet manually, then create the macro to remove A1:BB1119 it will work. -- Mary Frances "Joel" wrote: Excel is making sure you don't incorrectly delete additional information you still need. If excel did what youi want then data at higher rows above 1119 would also get deleted and data to the right of cell BB would get delete. These two additional areas would get deleted. A1120:BB65535 BC1:IV1119 "Mary Frances" wrote: I have a 9 mb file that I want to create a macro that will copy/paste/new workbook/creat a copy for one of the sheets, then copy/paste values some of the cells in the new workbook, then delete A1:BB1119. The macro I have written should work, but does not. If I delete the rows and then the columns, the rows will delete, but not the columns. If I delete the columns first, the rows will not delete. I am thinking Excel may have a limit on how many deletions it can do in a macro. Do you have any ideas on how I can accomplish this task? -- Mary Frances |
| All times are GMT +1. The time now is 10:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com