jejmiller
02-25-2020, 08:25 AM
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
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