Pasting and Deleting Error

[Arthur1]

Hi,

When I run the code below for some reason I keep getting an Excel error and Excel crashes? It happens before I delete some rows. I have noted below The point at which Excel crashes. Any help would be appreciated.



Public ProcessFile
Public AppDir$
Public old As Variant
Public current As Variant
Public myWB$
Public r As Long
Public DataRow As Long
Public CS_PB As String
Public Name As String


Sub PasteSpecial()
'ctrl-l shortcut macro

'paste formulas as values
Range(Cells(3, 3), Cells(Range("C3&quot.End(xlDown).Row, 4)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("C3&quot.Select

'format column
Columns("D&quot.Select
Selection.NumberFormat = "#,##0"
Columns("E:E&quot.Select
Selection.NumberFormat = "General"
Range("C3&quot.Select

'add formula for cap
Range(Cells(3, 5), Cells(Range(&quot;A3&quot.End(xlDown).Row, 5)).FormulaR1C1 = &quot;=IF(RC[-1]=&quot;&quot;&quot;&quot;,&quot;&quot;?&quot;&quot;,IF(RC[-1]=&quot;&quot;#N/A N.A.&quot;&quot;,&quot;&quot;?&quot;&quot;,IF(RC[-1]=&quot;&quot;#N/A Sec&quot;&quot;,&quot;&quot;?&quot;&quot;,IF(RC[-1]>=2500000000,&quot;&quot;L&quot;&quot;,IF(AND(RC[-1]>=0,RC[-1]<2500000000),&quot;&quot;S&quot;&quot;,&quot;&quot;?&quot;&quot))))&quot;

'get sheet ready to loop through to change the date to character
Columns(&quot;H:H&quot.Select
Selection.Insert Shift:=xlToRight

'loop through cells to update data
r = 3

Do Until Cells(r, 9).Value = &quot;&quot;
If Cells(r, 9).Value <> &quot;&quot; Then
Cells(r, 1).Value = Format(Cells(r, 1), &quot;>&quot
Cells(r, 4).Select
ActiveCell.FormulaR1C1 = Cells(r, 4).Value
Cells(r, 8).Select
ActiveCell.FormulaR1C1 = _
&quot;=TEXT(YEAR(RC[-1]),&quot;&quot;0000&quot;&quot& TEXT(MONTH(RC[-1]),&quot;&quot;00&quot;&quot& TEXT(DAY(RC[-1]),&quot;&quot;00&quot;&quot&quot;

End If
r = r + 1
Loop

Columns(&quot;H:H&quot.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range(&quot;H1&quot.Value = &quot;Trade Date&quot;

ActiveWorkbook.Save

Deleting

End Sub


***********************HERE**********************
Sub Deleting()
Columns(&quot;B:H&quot.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Rows(&quot;2:2&quot.Select
Selection.Delete Shift:=xlUp

Columns(&quot;I:I&quot.Select
Selection.Delete Shift:=xlToLeft
Range(&quot;A1&quot.Select
End Sub

 

[IfThenElvis]

Hi Arthur, you can't call your Procedure 'Sub PasteSpecial' as it is already defined as an Excel operation, call it &quot;PasteSpecialXX' or something else. You may also have to rename Public Name As String.
When I make these changes it runs OK for me. Doug

 

[Arthur1]

No, it did not work even with those changes, it still keep crashing inside the deleteing function.

 

[IfThenElvis]

I tried moving the macros from a Module1 to the Workbook module to a Worksheet module & it still worked.
Try removing &quot;Deleting&quot; from the PasteSpecialXX macro and then run each macro separately. Which one bombs out?
I'm using Excel 2000.
Is your sheet protected? Do you have any Event macros in your workbook?
I suspect that there is something in your data that results in a cell error when you delete rows and columns.

Not too sure what else to suggest but I like a challenge.

 

[Arthur1]

The sheet was created in Excel XP but I am debugging it in 97. No protection on this sheet either. The Paste Macro run perfectly, the Delete Macro keeps bombing. I ran each sepratly and even tried various other methods for deleting that Row. It still fails.
After running the paste macro I can't even delete the row manually! Any Ideas?

 

[Arthur1]

Looks like a data issue to me as well. I will try and check it out.