Macro limitations

M

Mary Frances

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?
 
J

Joel

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
 
M

Mary Frances

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


[QUOTE="Don Guillett"]
Yes, Post your code.
 
D

Don Guillett

==========
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
(e-mail address removed)
Mary Frances said:
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
Click to expand...
 
M

Mary Frances

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 said:
==========
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
(e-mail address removed)
Mary Frances said:
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


[QUOTE="Don Guillett"]
Yes, Post your code.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
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?[/QUOTE][/QUOTE]
[/QUOTE]
Click to expand...
Click to expand...
 
D

Don Guillett

Send to my address below and I'll have a look.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Mary Frances said:
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 said:
==========
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
(e-mail address removed)
Mary Frances said:
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


:

Yes, Post your code.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
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?[/QUOTE]
[/QUOTE][/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
M

Mary Frances

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 said:
Send to my address below and I'll have a look.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Mary Frances said:
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 said:
==========
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
(e-mail address removed)
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


:

Yes, Post your code.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
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?[/QUOTE][/QUOTE]
[/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
M

Mary Frances

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sharing workbooks limitations - Merged Cells 12
How to copy a range of cells and paste into a new workbook in differentcolumns 3
macro to filter 6
Macro to hide rows with zero values in columns 2
macro to hide columns with zero values? 2
Problem with macro 3
File Size 3
Need help deleting data 2

Top