0

I am relatively new to VBA and am trying to create a dashboard with a really long VBA code. The code is used to look-up values from multiple sheets, as well as replace any #N/A errors or 0's with a blank cell, and unprotect and protect the sheet. The code itself works, but runs incredibly slowly and freezes the Excel sheet. Any tips on how to make the code run faster would be greatly appreciated!

Sub RunDashboard()

Sheets("Dashboard").Select
ActiveSheet.Unprotect

Range("D2:D" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C4:R10000C4"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,4,FALSE)"

Range("E2:E" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C5:R10000C5"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,5,FALSE)"

Range("F2:F" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C6:R10000C6"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,6,FALSE)"

Range("G2:G" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C7:R10000C7"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,7,FALSE)"

Range("H2:H" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C8:R10000C8"
Selection.FormulaR1C1 =         
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,8,FALSE)"

Range("I2:I" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C9:R10000C9"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,9,FALSE)"

Range("K2:K" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C11:R10000C11"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,11,FALSE)"

Range("P2:P" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C16:R10000C16"
Selection.FormulaR1C1 =     
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,16,FALSE)"

Range("T2:T" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C20:R10000C20"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,20,FALSE)"

Range("X2:X" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C24:R10000C24"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,24,FALSE)"

Range("Y2:Y" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C25:R10000C25"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,25,FALSE)"

Range("Z2:Z" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C26:R10000C26"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,26,FALSE)"

Range("AA2:AA" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C27:R10000C27"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,27,FALSE)"

Range("AB2:AB" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C28:R10000C28"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,28,FALSE)"

Range("AH2:AH" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C34:R10000C34"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,34,FALSE)"

Range("AI2:AI" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C35:R10000C35"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,35,FALSE)"

Range("AJ2:AJ" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C36:R10000C36"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,36,FALSE)"

Range("AK2:AK" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C37:R10000C37"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,37,FALSE)"

Range("AL2:AL" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C38:R10000C38"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,38,FALSE)"

Range("AM2:AM" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C39:R10000C39"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,39,FALSE)"

Range("AN2:AN" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C40:R10000C40"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,40,FALSE)"

Range("AO2:AO" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C41:R10000C41"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,41,FALSE)"

Range("AP2:AP" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C42:R10000C42"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C45,42,FALSE)"

Range("AQ2:AQ" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C43:R10000C43"
Selection.FormulaR1C1 = 
"=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C45,43,FALSE)"


Range("L2:L" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C11:R10000C11"
Selection.FormulaR1C1 = _

"=IF(RC2=""Deleted"",VLOOKUP(RC1,MasterAssetList!R2C1:R10000C45,12,FALSE), 
VLOOKUP(RC1, DataPull!R2C1:R10000C62,3, FALSE))"

Range("M2:M" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C13:R10000C13"
Selection.FormulaR1C1 = _

"=IF(RC2=""Deleted"",VLOOKUP(RC1,MasterAssetList!R2C1:R10000C45,13,FALSE), 
VLOOKUP(RC1, DataPull!R2C1:R10000C62,21, FALSE))"

Range("N2:N" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C14:R10000C14"
Selection.FormulaR1C1 = _

"=IF(RC2=""Deleted"",VLOOKUP(RC1,MasterAssetList!R2C1:R10000C45,14,FALSE), 
VLOOKUP(RC1, DataPull!R2C1:R10000C62,22, FALSE))"


Range("O2:O" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C15:R10000C15"
Selection.FormulaR1C1 = _

"=IF(RC2=""Deleted"",VLOOKUP(RC1,MasterAssetList!R2C1:R10000C45,15,FALSE), 
VLOOKUP(RC1, DataPull!R2C1:R10000C62,4, FALSE))"

Range("Q2:Q" & Cells(Rows.Count, "A").End(xlUp).Row).Select
ActiveCell.FormulaR1C1 = _

"=IF(RC2=""Deleted"",VLOOKUP(RC1,MasterAssetList!R2C1:R10000C45,17,FALSE), 
VLOOKUP(RC1, DataPull!R2C1:R10000C62,7, FALSE))"


Range("R2:R" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C18:R10000C18"
Selection.FormulaR1C1 = _

"=IF(RC2=""Deleted"",VLOOKUP(RC1,MasterAssetList!R2C1:R10000C45,18,FALSE), 
VLOOKUP(RC1, DataPull!R2C1:R10000C62,18, FALSE))"

Range("S2:S" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C19:R10000C19"
Selection.FormulaR1C1 = _

"=IF(RC2=""Deleted"",VLOOKUP(RC1,MasterAssetList!R2C1:R10000C45,19,FALSE), 
VLOOKUP(RC1, DataPull!R2C1:R10000C62,14, FALSE))"


Range("U2:U" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C21:R10000C21"
Selection.FormulaR1C1 = _

"=IF(RC2=""Deleted"",VLOOKUP(RC1,MasterAssetList!R2C1:R10000C45,21,FALSE), 
VLOOKUP(RC1, DataPull!R2C1:R10000C62,5, FALSE))"


Range("V2:V" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C22:R10000C22"
Selection.FormulaR1C1 = _

"=IF(RC2=""Deleted"",VLOOKUP(RC1,MasterAssetList!R2C1:R10000C45,22,FALSE), 
VLOOKUP(RC1, DataPull!R2C1:R10000C62,30, FALSE))"

Range("W2:W" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C23:R10000C23"
Selection.FormulaR1C1 = _

"=IF(RC2=""Deleted"",VLOOKUP(RC1,MasterAssetList!R2C1:R10000C45,23,FALSE), 
VLOOKUP(RC1, DataPull!R2C1:R10000C62,31, FALSE))"


Range("AD2:AD" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C30:R10000C30"
Selection.FormulaR1C1 = _

"=IF(RC2=""Deleted"",VLOOKUP(RC1,MasterAssetList!R2C1:R10000C45,30,FALSE), 
VLOOKUP(RC1, DataPull!R2C1:R10000C62,23, FALSE))"

Range("AE2:AE" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C31:R10000C31"
Selection.FormulaR1C1 = _

"=IF(RC2=""Deleted"",VLOOKUP(RC1,MasterAssetList!R2C1:R10000C45,31,FALSE), 
VLOOKUP(RC1, DataPull!R2C1:R10000C62,24, FALSE))"

Range("AF2:AF" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C32:R10000C32"
Selection.FormulaR1C1 = _

"=IF(RC2=""Deleted"",VLOOKUP(RC1,MasterAssetList!R2C1:R10000C45,32,FALSE), 
VLOOKUP(RC1, DataPull!R2C1:R10000C62,52, FALSE))"


Sheets("Dashboard").Select
On Error Resume Next
With Range("A2:A10000")
.Value = .Value
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With

Sheets("Dashboard").Select
Dim c As Range, LastRow As Long
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
For Each c In Range("A1:AQ1" & LastRow)
If Application.IsNA(c.Value) Then
c.Value = ""
End If
Next

Dim rng As Range
For Each rng In Range("A1:AQ1" & LastRow)
    If rng.Value = 0 Then
       rng.Value = ""
    End If
Next

Sheets("Dashboard").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowFormattingCells:=True, AllowSorting:=True, AllowFiltering:=True

MsgBox "Your dashboard has finished running."

End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Victoria G
  • 81
  • 2
  • 8
  • 2
    [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?rq=1) – J.Doe Jun 26 '18 at 01:01

3 Answers3

1

  1. The first thing I would do to streamline the code would be to save a variable for the last row of A:

    Dim intLastRow as integer
intLastRow = Cells(Rows.Count, "A").End(xlUp).Row

    So that each time you use your code like this:

    Range("D2:D" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Application.Goto Reference:="R2C4:R10000C4"
Selection.FormulaR1C1 = "=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,4,FALSE)"

    Could be reduced to:

    Range("D2:D" & intLastRow).Select
Application.Goto Reference:="R2C4:R10000C4"
Selection.FormulaR1C1 = "=VLOOKUP(RC1,MasterAssetList!R2C1:R10000C41,4,FALSE)"

    This means you aren't constantly asking the application to find the last row of column A, only once. That should scrape off a considerable amount of time.

  2. As J.Doe says in the comments, go back through your code and replace all your uses of 'Select' with hard-coded ranges. This link they provided has a great answer about ways to avoid using 'Select'.

girlvsdata
  • 1,596
  • 11
  • 21
1

In addition to the suggestions made by @girlvsdata and @jdoe, you can speed things up by preventing Excel from updating the display and calculating all formulas every time you switch from one cell to the next or alter data.

Do this at the beginning of your sub:

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

And then do this at the end to re-enable:

Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

That, in addition to the other suggestions, should help quite a bit.

SeanW333
  • 479
  • 4
  • 9
0

Thank you @girlvsdata and @jdoe and @seanW333 - all of those were very helpful and made it run incredibly faster! Final code is posted below for anyone who may want to see examples.

Sub RunDashboard2()

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Sheets("Dashboard").Unprotect
Sheets("DataPull").Unprotect
Sheets("MasterAssetList").Unprotect

Sheets("Dashboard").Range("C:C").Value = Sheets("DataPull").Range("B:B").Value
Sheets("Dashboard").Range("A:A").Value = Sheets("DataPull").Range("A:A").Value

Sheets("Dashboard").Select
Range("B2:B" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Selection.FormulaR1C1 = _
    "=IF(ISNA(VLOOKUP(RC1,MasterAssetList!R2C1:R10000C1,2,FALSE)),""Newly     Inserted"","""")"
Columns("B:B").Select
Range("B2").Activate
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
    Formula1:="=""Newly Inserted"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
    .Bold = True
    .Italic = False
    .ThemeColor = xlThemeColorAccent3
    .TintAndShade = -0.499984740745262
End With
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent3
    .TintAndShade = 0.799981688894314
End With
Selection.FormatConditions(1).StopIfTrue = False

Sheets("MasterAssetList").Select
    Range("B2:B" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Selection.FormulaR1C1 = _
    "=IF(ISNA(VLOOKUP(RC1,Dashboard!R2C1:R10000C1,1,FALSE)),""Deleted"","""")"
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
    Formula1:="=""Deleted"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
    .Bold = True
    .Italic = False
    .ThemeColor = xlThemeColorAccent2
    .TintAndShade = -0.499984740745262
End With
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent2
    .TintAndShade = 0.799981688894314
End With
Selection.FormatConditions(1).StopIfTrue = False

Sheets("MasterAssetList").Select
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A1:$C1").AutoFilter Field:=2, Criteria1:="<>"
Range("A2:C2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Sheets("Dashboard").Select
Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste

Dim MAL As Worksheet
Set MAL = Sheets("MasterAssetList")

MAL.Select
Selection.RowHeight = 14.5

Sheets("Dashboard").Select
On Error Resume Next
With Range("A2:A10000")
.Value = .Value
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With

Dim DB As Worksheet
Set DB = Sheets("Dashboard")
DB.Select
Selection.RowHeight = 14.5

Dim intLastRow As Integer
intLastRow = Sheets("Dashboard").Cells(Rows.Count, "A").End(xlUp).Row

Range("D2:D" & intLastRow).Formula = "=VLOOKUP($A2,MasterAssetList!$A$2:$AQ$10000,4,FALSE)"
Range("D2:D" & intLastRow).Formula = "=VLOOKUP($A2,MasterAssetList!$A$2:$AQ$10000,4,FALSE)"
Range("E2:E" & intLastRow).Formula = "=VLOOKUP($A2,MasterAssetList!$A$2:$AQ$10000,5,FALSE)"
Range("F2:F" & intLastRow).Formula = "=VLOOKUP($A2,MasterAssetList!$A$2:$AQ$10000,6,FALSE)"
Range("G2:G" & intLastRow).Formula = "=VLOOKUP($A2,MasterAssetList!$A$2:$AQ$10000,7,FALSE)"
Range("H2:H" & intLastRow).Formula = "=VLOOKUP($A2,MasterAssetList!$A$2:$AQ$10000,8,FALSE)"
Range("I2:I" & intLastRow).Formula = "=VLOOKUP($A2,MasterAssetList!$A$2:$AQ$10000,9,FALSE)"
Range("K2:K" & intLastRow).Formula = "=VLOOKUP($A2,MasterAssetList!$A$2:$AQ$10000,11,FALSE)"
Range("P2:P" & intLastRow).Formula = "=VLOOKUP($A2,MasterAssetList!$A$2:$AQ$10000,16,FALSE)"
Range("T2:T" & intLastRow).Formula = "=VLOOKUP($A2,MasterAssetList!$A$2:$AQ$10000,20,FALSE)"
Range("X2:X" & intLastRow).Formula = "=VLOOKUP($A2,MasterAssetList!$A$2:$AQ$10000,24,FALSE)"
Range("Y2:Y" & intLastRow).Formula = "=VLOOKUP($A2,MasterAssetList!$A$2:$AQ$10000,25,FALSE)"
Range("Z2:Z" & intLastRow).Formula = "=VLOOKUP($A2,MasterAssetList!$A$2:$AQ$10000,26,FALSE)"
Range("AA2:AA" & intLastRow).Formula = "=VLOOKUP($A2,MasterAssetList!$A$2:$AQ$10000,27,FALSE)"
Range("AB2:AB" & intLastRow).Formula = "=VLOOKUP($A2,MasterAssetList!$A$2:$AQ$10000,28,FALSE)"
Range("AH2:AH" & intLastRow).Formula = "=VLOOKUP($A2,MasterAssetList!$A$2:$AQ$10000,34,FALSE)"    
Range("AI2:AI" & intLastRow).Formula = "=VLOOKUP($A2,MasterAssetList!$A$2:$AQ$10000,35,FALSE)"
Range("AJ2:AJ" & intLastRow).Formula = "=VLOOKUP($A2,MasterAssetList!$A$2:$AQ$10000,36,FALSE)"
Range("AK2:AK" & intLastRow).Formula = "=VLOOKUP($A2,MasterAssetList!$A$2:$AQ$10000,37,FALSE)"
Range("AL2:AL" & intLastRow).Formula = "=VLOOKUP($A2,MasterAssetList!$A$2:$AQ$10000,38,FALSE)"
Range("AM2:AM" & intLastRow).Formula = "=VLOOKUP($A2,MasterAssetList!$A$2:$AQ$10000,39,FALSE)"
Range("AN2:AN" & intLastRow).Formula = "=VLOOKUP($A2,MasterAssetList!$A$2:$AQ$10000,40,FALSE)"
Range("AO2:AO" & intLastRow).Formula = "=VLOOKUP($A2,MasterAssetList!$A$2:$AQ$10000,41,FALSE)"
Range("AP2:AP" & intLastRow).Formula = "=VLOOKUP($A2,MasterAssetList!$A$2:$AQ$10000,42,FALSE)"
Range("AQ2:AQ" & intLastRow).Formula = "=VLOOKUP($A2,MasterAssetList!$A$2:$AQ$10000,43,FALSE)"

Range("L2:L" & intLastRow).Formula = "=IF($B2=""Deleted"", VLOOKUP($A2, 'MasterAssetList'!$A$2:$AQ$10000, 12, FALSE), VLOOKUP($A2, 'DataPull'!$A$2:$BK$10000, 3, FALSE))"
Range("M2:M" & intLastRow).Formula = "=IF($B2=""Deleted"", VLOOKUP($A2, 'MasterAssetList'!$A$2:$AQ$10000, 13, FALSE), VLOOKUP($A2, 'DataPull'!$A$2:$BK$10000, 21, FALSE))"

Range("N2:N" & intLastRow).Formula = "=IF($B2=""Deleted"", VLOOKUP($A2, 'MasterAssetList'!$A$2:$AQ$10000, 14, FALSE), VLOOKUP($A2, 'DataPull'!$A$2:$BK$10000, 22, FALSE))"

Range("O2:O" & intLastRow).Formula = "=IF($B2=""Deleted"", VLOOKUP($A2, 'MasterAssetList'!$A$2:$AQ$10000, 15, FALSE), VLOOKUP($A2, 'DataPull'!$A$2:$BK$10000, 4, FALSE))"
Range("Q2:Q" & intLastRow).Formula = "=IF($B2=""Deleted"", VLOOKUP($A2, 'MasterAssetList'!$A$2:$AQ$10000, 17, FALSE), VLOOKUP($A2, 'DataPull'!$A$2:$BK$10000, 7, FALSE))"

Range("R2:R" & intLastRow).Formula = "=IF($B2=""Deleted"", VLOOKUP($A2,     'MasterAssetList'!$A$2:$AQ$10000, 18, FALSE), VLOOKUP($A2, 'DataPull'!$A$2:$BK$10000, 18, FALSE))"

Range("S2:S" & intLastRow).Formula = "=IF($B2=""Deleted"", VLOOKUP($A2, 'MasterAssetList'!$A$2:$AQ$10000, 19, FALSE), VLOOKUP($A2, 'DataPull'!$A$2:$BK$10000, 14, FALSE))"

Range("U2:U" & intLastRow).Formula = "=IF($B2=""Deleted"", VLOOKUP($A2, 'MasterAssetList'!$A$2:$AQ$10000, 21, FALSE), VLOOKUP($A2, 'DataPull'!$A$2:$BK$10000, 5, FALSE))"

Range("V2:V" & intLastRow).Formula = "=IF($B2=""Deleted"", VLOOKUP($A2, 'MasterAssetList'!$A$2:$AQ$10000, 22, FALSE), VLOOKUP($A2, 'DataPull'!$A$2:$BK$10000, 30, FALSE))"

Range("W2:W" & intLastRow).Formula = "=IF($B2=""Deleted"", VLOOKUP($A2, 'MasterAssetList'!$A$2:$AQ$10000, 23, FALSE), VLOOKUP($A2, 'DataPull'!$A$2:$BK$10000, 31, FALSE))"

Range("AD2:AD" & intLastRow).Formula = "=IF($B2=""Deleted"", VLOOKUP($A2,     'MasterAssetList'!$A$2:$AQ$10000, 30, FALSE), VLOOKUP($A2, 'DataPull'!$A$2:$BK$10000, 23, FALSE))"

Range("AE2:AE" & intLastRow).Formula = "=IF($B2=""Deleted"", VLOOKUP($A2, 'MasterAssetList'!$A$2:$AQ$10000, 31, FALSE), VLOOKUP($A2, 'DataPull'!$A$2:$BK$10000, 24, FALSE))"

Range("AF2:AF" & intLastRow).Formula = "=IF($B2=""Deleted"", VLOOKUP($A2, 'MasterAssetList'!$A$2:$AQ$10000, 32, FALSE), VLOOKUP($A2, 'DataPull'!$A$2:$BK$10000, 52, FALSE))"

Sheets("Dashboard").Select
On Error Resume Next
With Range("A2:A10000")
.Value = .Value
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With

Sheets("Dashboard").Activate
Dim c As Range
intLastRow = Cells(Rows.Count, "A").End(xlUp).Row

Range("A2:AQ2" & intLastRow).NumberFormat = "0;0;"""""
For Each c In Range("A2:AQ2" & intLastRow)
If Application.IsNA(c.Value) Then
c.Value = ""
End If
Next

Sheets("DataPull").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowFormattingCells:=True, AllowSorting:=True, AllowFiltering:=True

Sheets("MasterAssetList").Protect DrawingObjects:=True, Contents:=True,     Scenarios:=True _
    , AllowFormattingCells:=True, AllowSorting:=True, AllowFiltering:=True

Sheets("Dashboard").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowFormattingCells:=True, AllowSorting:=True, AllowFiltering:=True
Sheets("Dashboard").Select

MsgBox "Your dashboard has finished running."

Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Victoria G
  • 81
  • 2
  • 8