Why will 2003 macro run in compatibility mode, but not when converted to 2007?

J

jmthompson

Well-known Member
Joined
Mar 31, 2008
Messages
966
Good morning,
I have 6 days to test and rebuild my macros in 2007 before I will be forced to upgrade my OS from 2003. One of my templates has 5 large macros. When testing the first (which I cannot progress beyond), in an .xlsm file, I get runtime error 7, Out of memory. When I run the same macro, using the same data in the .xls file using compatibility mode, I have no problems.

Is it safe to rely on compatibility mode for this very important process?

Any ideas what changes I can make to the existing macro to allow if to work in a 2007 format?

Debug shows run-time error here:
Code: 
Range("F:F").Value = Range("F:F").Value

Code: 
Sub Reformat_Part1()
 
Sheets("Data Dump").Select
Dim lastRow As Integer
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("Z2:Z" & lastRow).ClearContents
Range("AA2:AA" & lastRow).ClearContents
Range("A1:AZ" & lastRow).Select
Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Selection.Subtotal GroupBy:=5, Function:=xlSum, TotalList:=Array(3), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    ActiveSheet.Outline.ShowLevels RowLevels:=2
 
    Range("C:C").Style = "Currency"
 
    MsgBox ("Do the totals match the central bills?")
 
    Cells.Select
    Selection.RemoveSubtotal
Application.ScreenUpdating = False
Dim SiteCol As Range, cell As Range
Dim wsDest As Worksheet
Dim i As Long
Set wsDest = Workbooks("Conversion template.xls").Worksheets("Template")
i = wsDest.Cells(Rows.Count, 2).End(xlUp).Row
If (i = 2) And (wsDest.Cells(2, 1) = "") Then i = 0
Set SiteCol = Range("A2")
Set SiteCol = Range(SiteCol, Cells(Rows.Count, SiteCol.Column).End(xlUp))
For Each cell In SiteCol.Cells
If cell.Value <> "" Then
i = i + 1
wsDest.Cells(i, 1).Resize(1, 4) = Array(cell.Offset(0, 4), cell.Offset(0, 5), cell.Offset(0, 6), cell.Offset(0, 2))
End If
Next
Sheets("Template").Select
Range("A1").Value = "Business Unit"
Range("B1").Value = "Department"
Range("C1").Value = "Account"
Range("D1").Value = "Amount"
 
Range("B:B").Insert Shift:=xlToRight
Range("B1").Select
ActiveCell.FormulaR1C1 = "Ledger"
 
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
With Range("B2:B" & lastRow)
.Value = "ACTUALS"
End With
Range("E:E").Insert Shift:=xlToRight
Range("E1").Select
ActiveCell.FormulaR1C1 = "Edition"
 
Range("F:F").Insert Shift:=xlToRight
Range("F1").Select
ActiveCell.FormulaR1C1 = "Project ID"
Selection.NumberFormat = "General"
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
With Range("F2:F" & lastRow)
.FormulaR1C1 = "=CONCATENATE(""000"",RIGHT('Data Dump'!RC[+2],3))"
End With
Range("F:F").Select
Selection.NumberFormat = "@"
 
'runtime error here
Range("F:F").Value = Range("F:F").Value
Range("G:G").Insert Shift:=xlToRight
Range("G:G").Select
    Selection.NumberFormat = "General"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Local 1"
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
With Range("G2:G" & lastRow)
.Formula = "=Left('Data Dump'!RC[+1],3)"
End With
Range("G:G").Value = Range("G:G").Value
 
Range("H:H").Insert Shift:=xlToRight
Range("H1").Select
ActiveCell.FormulaR1C1 = "Local 2"
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
With Range("H2:H" & lastRow)
.Formula = "=MID('Data Dump'!RC,5,3)"
End With
Range("H:H").Select
Selection.NumberFormat = "00#"
Range("H:H").Value = Range("H:H").Value
Range("I:I").Insert Shift:=xlToRight
Range("I1").Select
ActiveCell.FormulaR1C1 = "Affiliate"
Range("K:K").Insert Shift:=xlToRight
Range("K1").Select
ActiveCell.FormulaR1C1 = "Stat Amount"
Range("L:L").Insert Shift:=xlToRight
Range("L1").Select
ActiveCell.FormulaR1C1 = "UOM"
Range("M:M").Insert Shift:=xlToRight
Range("M1").Select
ActiveCell.FormulaR1C1 = "Open"
Columns("J:J").Select
Selection.NumberFormat = "0.00"
Columns("F:F").Select
    Selection.Replace What:="000NUL", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
Range("F:F").Select
Selection.NumberFormat = "@"
Range("G:H").Select
Selection.NumberFormat = "00#"
Columns("G:H").Select
    Selection.Replace What:="NUL", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
Range("G:H").Select
Selection.NumberFormat = "@"
Range("N1").Value = "Description"
Range("N:N").Select
Selection.NumberFormat = "General"
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
With Range("N2:N" & lastRow)
.FormulaR1C1 = "=CONCATENATE('Data Dump'!RC[+2],""-"",LEFT('Data Dump'!RC[-10],7),""-"",LEFT('Data Dump'!RC[+37],895))"
End With
Range("N:N").Value = Range("N:N").Value
Cells.Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("N2") _
        , Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False _
        , Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:= _
        xlSortNormal
 
    Dim c As Range
        For Each c In Range([D2], Cells(Rows.Count, "D").End(xlUp))
            If c < 500000 Then c.Offset(, -1) = ""
        Next c
Range("E:E").Select
Selection.NumberFormat = "@"
        For Each c In Range([D2], Cells(Rows.Count, "D").End(xlUp))
            If c < 500000 And c > 399999 Then c.Offset(, 1) = "00"
        Next c
 
    Cells.Select
    Cells.EntireColumn.AutoFit
 
Set wsDest = Workbooks("Conversion template.xls").Worksheets("Staten Island Advance")
i = wsDest.Cells(Rows.Count, 2).End(xlUp).Row
If (i = 2) And (wsDest.Cells(2, 1) = "") Then i = 0
Set SiteCol = Range("A2")
Set SiteCol = Range(SiteCol, Cells(Rows.Count, SiteCol.Column).End(xlUp))
For Each cell In SiteCol.Cells
If cell.Value = "13400" Then
i = i + 1
wsDest.Cells(i, 1).Resize(1, 14) = Array(cell, cell.Offset(0, 1), cell.Offset(0, 2), cell.Offset(0, 3), cell.Offset(0, 4), cell.Offset(0, 5), cell.Offset(0, 6), cell.Offset(0, 7), cell.Offset(0, 8), cell.Offset(0, 9), cell.Offset(0, 10), cell.Offset(0, 11), cell.Offset(0, 12), cell.Offset(0, 13))
End If
Next
Sheets("Staten Island Advance").Select
MyDate = DateSerial(Year(Date), Month(Date) - 1, 1)
MyMonth = Format(MyDate, "mmmm")
MyYear = Year(MyDate)
   lastRow = Cells(Rows.Count, "A").End(xlUp).Row
   Cells(lastRow, "A").Copy Destination:=Cells(lastRow + 1, "A")
   Cells(lastRow, "B").Copy Destination:=Cells(lastRow + 1, "B")
   Cells(lastRow + 1, "D").Value = 210104
   Cells(lastRow + 1, "J").Formula = "=-sum(J2:J" & lastRow & ")"
   Cells(lastRow + 1, "N").Value = "Clear " & MyMonth & " " & MyYear & " P-Card Payment"
   Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
Range("A1").Value = "Business Unit"
Range("B1").Value = "Ledger"
Range("C1").Value = "Department"
Range("D1").Value = "Account"
Range("E1").Value = "Edition"
Range("F1").Value = "Project ID"
Range("G1").Value = "Local 1"
Range("H1").Value = "Local 2"
Range("I1").Value = "Affiliate"
Range("J1").Value = "Amount"
Range("K1").Value = "Stat Amount"
Range("L1").Value = "UOM"
Range("M1").Value = "Open"
Range("N1").Value = "Description"
Sheets("Template").Select
Columns("N:N").Select
    Selection.Replace What:=" - ", Replacement:="-", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" & ", Replacement:="&", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" / ", Replacement:="/", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="     ", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="    ", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="   ", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="  ", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" for ", Replacement:="/", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="-for ", Replacement:="-", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" and ", Replacement:="&", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" / ", Replacement:="/", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=",", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=";", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="national", Replacement:="natnl", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="accounting", Replacement:="acct", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="package", Replacement:="pkg", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="convention", Replacement:="conv.", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="DHLW ", Replacement:="DHL", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="DHLX ", Replacement:="DHL", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        Selection.Replace What:="DHLX*08", Replacement:="DHL", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="magazine", Replacement:="mag.", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="marketing", Replacement:="mrktg", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="Invoice", Replacement:="Inv.", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="department", Replacement:="dept", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="advertising", Replacement:="adver", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="production", Replacement:="prod", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="committee", Replacement:="comte", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="year", Replacement:="yr", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="month", Replacement:="mo", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="january", Replacement:="jan", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="february", Replacement:="feb", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="August", Replacement:="aug", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="September", Replacement:="Sept", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="October", Replacement:="Oct", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="November", Replacement:="Nov", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="December", Replacement:="Dec", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="program", Replacement:="prog", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="accessory", Replacement:="acc", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="accessories", Replacement:="acc", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="included", Replacement:="inc", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="subscription", Replacement:="sub", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="replacement", Replacement:="repl", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="miscellaneous", Replacement:="misc", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="membership", Replacement:="memb.", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="renewal", Replacement:="rnwl", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="service", Replacement:="srvc", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="room", Replacement:="rm", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="payment", Replacement:="pymnt", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="i bought ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" bought ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" purchased ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="repaired", Replacement:="repd.", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="repair", Replacement:="rep.", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" of ", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="meeting", Replacement:="mtg", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="supplies ", Replacement:="supp", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="return ", Replacement:="rtrn", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="returned ", Replacement:="rtrnd", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="requested by ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="gift card", Replacement:="gc", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="gift cards", Replacement:="gc", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="giftcards", Replacement:="gc", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="giftcard", Replacement:="gc", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="allocated ", Replacement:="alloc", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="P O ", Replacement:="po", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="po #", Replacement:="po#", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="p o #", Replacement:="po#", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="p.o.", Replacement:="po#", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="number ", Replacement:="#", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="employee ", Replacement:="emp", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="customer ", Replacement:="cust", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="luncheon", Replacement:="lunch", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="entertainment", Replacement:="ent.", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="order #", Replacement:="or#", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="order#", Replacement:="or#", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="order ", Replacement:="or.", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="additional", Replacement:="addtnl", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="publishing", Replacement:="pub.", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="publisher", Replacement:="pub.", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="publish ", Replacement:="pub.", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="published", Replacement:="pub.", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="maintenance", Replacement:="maint.", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="register", Replacement:="reg.", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="registration", Replacement:="reg.", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="backup", Replacement:="b/up", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="circulation", Replacement:="circ.", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="certificates", Replacement:="cert.", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="certificate", Replacement:="cert.", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="seminar", Replacement:="sem.", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="packaging", Replacement:="pkg.", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="account#", Replacement:="acct#", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="Grand Rapids Press", Replacement:="GRP", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=" ordered by ", Replacement:=" ", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="ordered by ", Replacement:=" ", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="this is ", Replacement:="", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="supervisor", Replacement:="supe", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="purchased", Replacement:="purch", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="duplicate", Replacement:="dupe", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="general", Replacement:="gen.", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="office", Replacement:="off.", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="birthday", Replacement:="Bday", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="building", Replacement:="bldg.", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="credentials", Replacement:="creds", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 
Range("A2").Select
Application.ScreenUpdating = True
End Sub
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Compatibility mode imposes certain restrictions on the file (eg number of rows is 65536 not the 1million+ of xl2007). I suspect you are running out of memory because of the added burden of trying to copy from/to the 1miilion plus rows in an normal .xlsm file.

There isn't any need to do this to such a large range - you can locate the end of your data and just copy from start to end:

Code: 
Dim lngLastRow As Long   'variable to hold the last row with a value
 
With Sheets("YourSheetNameHere") 'amend as appropriate
 
  lngLastRow = .Cells(.Rows.Count,"F").End(xlUp).Row
 
  'the above effectively goes to the very last cell in column F and then does a Ctrl+Up Arrow to find the last occupied cell in column F.
  'now use this variable to specify the range you want to copy/paste:
 
  .Range("F1:F" & lngLastRow).Value = .Range("F1:F" & lngLastRow).Value
 
End With
 
Upvote 0
So simple, it's brilliant. I forgot to shift my thinking to accomodate my calculations for the limitless row capability.

Thanks. I will revise my macros accordingly and test.

BTW- love that Firefly episode!
 
Upvote 0
I LOVED Firefly - can't believe it got canned after just one series :-(

On the other hand, I have the Serenity DVD to watch this weekend :-D
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
How to loop to find and replace with the specific cells
Replies
2
Views
297
Linda Lo
L
J
  • Solved
Assistance with excel macro
Replies
3
Views
244
dreid1011
dreid1011
N
  • Question Question
Not understanding loop in vba
Replies
7
Views
296
jolivanes
jolivanes
L
  • Locked
  • Question Question
Macros and protecting a cell
Replies
5
Views
77
Fluff
Fluff
D
  • Solved
Range selection specific column with End(xlDown)
Replies
8
Views
296
dwalc
D

Forum statistics

Threads
1,223,237
Messages
6,170,924
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top