Speed Up VBA Macro

[jejmiller]

I read replacing "active" and "select" with an 'Array' will speed up my Macro's, though, not sure how exactly to go about that.

Any way I can speed this up? It's very laggy, and will sometimes crash excel.

Sub Macro2()'
' Macro2 Macro
'


'
    Columns("B:B").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Histry!C,1,FALSE)"
    Range("B2").Select
    LastRow = Range("A1").End(xlDown).Row
    Range("B2").AutoFill Destination:=Range("B2:B" & LastRow)
    Range("B1").AutoFilter
    Range("B1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$Z$200").AutoFilter Field:=2, Criteria1:="#N/A"
    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    ActiveSheet.ShowAllData
    ActiveSheet.Range("$A$1:$Z$200").AutoFilter Field:=2, Operator:= _
        xlFilterNoFill
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Columns("B:B").Select
    Selection.Delete Shift:=xlToLeft
    Selection.AutoFilter
    Range("F8").Select
End Sub

[Paul_Hossler]

Attach as sample workbook, and just tell us what you want to do

[p45cal]

try:

Sub Macro2()    '
LastRow = Range("A1").End(xlDown).Row
Columns(2).Insert
Range("B2:B" & LastRow).FormulaR1C1 = "=VLOOKUP(RC[-1],Histry!C,1,FALSE)"
ActiveSheet.Range("$A$1:$Z$200").AutoFilter Field:=2, Criteria1:="#N/A"
With ActiveSheet.AutoFilter
  Set DBRange = Intersect(.Range, .Range.Offset(1))
  DBRange.Columns(2).Interior.PatternTintAndShade = 0
  .Filters(2).Operator = xlFilterNoFill
  DBRange.SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp
  Columns(2).Delete
  .Range.AutoFilter
End With
End Sub

but I don't think it'll be much faster.
Do you have any cells in column A filled with colour before you run the macro? (Inserting a column carries the fill into the inserted column.) If not we can shorten it more.