VBA code exits before completing


Well-known Member
Aug 12, 2009
I'm using Excel 2003 and my code is supposed to delete the calculations leaving just the values. Everything seems to be working like it should 'till the code is supposed to delete the original data rows / columns:
Rows("1:67").Delete Shift:=xlUp
Columns("A:AJ").Delete Shift:=xlToLeft
The code deletes the rows and never gets to the columns-part. I tried adding MsgBox between the rows just to see if it's the Columns-part that's "crashing" the code but the code never got that far.

I tried to code to another workbook and everything went smoothly. Tried running the code from the other workbook to this one and it never got to the Columns-part. Tried swithing the row order and still only the first row completes.

There are a few named ranges in the the ranges the code is supposed to delete. Could this be causing the problems? The rest of the cells to be deleted are just values used in formulas in the range that I'm trying to move closer to A1 but the formulas are already converted to values when the code gets this far.

Is this a known bug or is there just something wrong with my workbook? And how could I get around it?

Here's the whole thing:
Sub ValuesOnly()
Dim SH As Shape
Dim Alue As Range
Dim Rng As Range

'Delete macro buttons:
    For Each SH In ActiveSheet.Shapes
    Next SH
'Convert formulas to values:
    With Range("AK:BM")
        .Value = .Value
    End With
'Delete the extra rows and columns:
    Rows("1:67").Delete Shift:=xlUp
    Columns("A:AJ").Delete Shift:=xlToLeft
End Sub

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.


Jun 19, 2002
I don't know if this will make a difference

Rows("1:67").Delete Shift:=xlShiftUp
Columns("A:AJ").Delete Shift:=xlShiftToLeft
Or you could omit the Shift argument as you are deleting entire rows and columns.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...