Problems Adding Columns--but data close to IV and no hidden objects

[Arnold]

Hello,
I have a problem similar to that in the thread "Problems deleting
columns" on Jul. 2. The recommendations, and resolution at
http://support.microsoft.com/default.aspx?kbid=211769 do not work in
my case. My sheet will probably contain up to cols. DD or EE, but
will never go to IV. There is no data in IV. However, I have
inserted conditional formatting and have filled rows/columns. I
continue to get the run-time error 1004 "To prevent the possible loss
of data, Microsoft Excel cannot shift nonblank cells off the
worksheet..." error. This is stumping me; any ideas?

Thanks,
Arnold

Sub mcrActivities()

Sheets("Fields").Select

Dim ActivityDate As Range
Set ActivityDate = ActiveSheet.Range("C11")
ActivityDate.Value = Date

Columns("C:C").Select
Selection.Copy
Sheets("Quarter").Select
lastcolumn = ActiveSheet.Cells.Find(what:="Activity",
searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
ActiveSheet.Cells(lastcolumn + 1).Offset(12, 0).Select

' Modified code to place new columns in front of the last column
with the text "Activity" in it on row 12; in the AAs.

ActiveSheet.Cells(lastcolumn).Offset(0, 0).EntireColumn.Insert

' Code to place the focus after a column insert.

ActiveSheet.Cells(lastcolumn).Offset(8, 0).Select

End Sub

 

[JLGWhiz]

You could have some hidden data in the columns or rows at or near the end of
the sheet. To check that, put the code below in the standard VB code module
and run it one sheet at the time.

Sub chkRng()
ActiveSheet.UsedRange.Select
End Sub

This will shade all cells on the sheet that fall within the outerboundries
of cells that contain data, whether visible or not. You can scroll right and
down to see what the limits are. If none of your columns or rows of the used
range are near the outer limits of the sheet, then check the code you are
running one step at a time to see what line of code throws the error and if
it infact attempts to shift too far left or right. Sometimes, using the
offset method of cell references can cause the problem. For example if the
active cell is in column C and you use:

ActiveCell.Offset(0, -5)

There are only two columns available to shift and that will throw the error
message because you are attempting to shift five.

 

[Arnold]

Thanks JLGWhiz,
Ok, this is what is now happening. I think it has to do something
with the fills and conditional formatting that I've set up across the
entire sheet. So, now, I've cleared all columns from FF to IV of all
formatting. The code above will insert "Activity" columns just fine
now, but when I delete one of the columns (using Ctrl -), column IV
returns to/contains the fills/conditional formatting that I previously
cleared. For example, if I add 3 Activity columns, then delete 2 of
them, cols. IV and IU will become formatting. Then, when I try to add
another Activity col in the AA range, the error 1004 "To prevent the
possible loss... msg appears. What causes this?

 

[JLGWhiz]

I am not technically astute enough to give you a good answer to that
question. I would be guessing because I am not educated in the digital
construction of the .xls spreadsheet. I know that when you add or delete
rows and columns in xl95 thru xl2003 that there are still 256 x 65536 cells
no matter how many time you add or delete. How those deleted cells are
handled internally in memory is a mystery to me. There might be a way to
code around the anomaly although I have never tried it, because I have never
had the problem. I am thinking maybe when you execute the code that causes
the error message, you could insert a line to clear columns IU and IV or some
similar type work around. Again, I'm guessing at this point.

 

[Arnold]

Thanks again.