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 Subbut 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.