Solved

Slow VBA Code, looking for help speeding up code

Posted on 2014-10-29
6
348 Views
Last Modified: 2014-11-13
I have a script that will import data, manipulate the data into usable charts. Currently the code takes a couple of minutes for 44000 lines, but I will be feeding in over 100000 lines and would like to optimize the code and get any feedback I can.

Here is the code:
Sub openDialog()
    Dim fd As Office.FileDialog
    Dim wbThis As Workbook
    Dim ws As Worksheet
    Dim PvtTbl As PivotTable
    Dim wsData As Worksheet
    Dim rngData As Range
    Dim PvtTblCache As PivotCache
    Dim wsPvtTbl As Worksheet
    Dim pvtFld As PivotField

    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    With fd
        .AllowMultiSelect = False
      
        ' Set the title of the dialog box.
        .Title = "Please select the file."
      
        ' Clear out the current filters, and add our own.
        .Filters.Clear
        .Filters.Add "All Files", "*.*"
        
        ' Show the dialog box. If the .Show method returns True, the
        ' user picked at least one file. If the .Show method returns
        ' False, the user clicked Cancel.
        If .Show = True Then
            wbTarget = .SelectedItems(1) 'replace txtFileName with your textbox
        End If
    End With
    
    Application.StatusBar = False
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    Set wbThis = ActiveWorkbook
    strName = ActiveSheet.Name
    Set wbTarget = Workbooks.Open(wbTarget)
    wbTarget.Sheets(1).Range("A1").Resize(Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row, Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, LookIn:=xlValues).Column).Copy
    wbThis.Activate
    Sheets("vulnerabilities").Range("A1").PasteSpecial
    wbTarget.Close
    Sheets("vulnerabilities").Select
    numrowsRaw = ActiveSheet.UsedRange.Rows.Count
' =========================================================================================
' RAW LOGS RAW LOGS RAW LOGS RAW LOGS RAW LOGS RAW LOGS RAW LOGS RAW LOGS RAW LOGS RAW LOGS
' =========================================================================================
    Columns("C:C").Select
    Selection.Cut
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Merged Hostnames"
    Range("A1").Select
    Range("C2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(ISBLANK(RC[-2]),RC[-1],(LEFT(RC[-2],(FIND("","",RC[-2])-1)))),RC[-2])"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C" & numrowsRaw)
    Range("C2:C" & numrowsRaw).Select
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "IP Address"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Vulnerability Name"
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.AutoFilter
    Selection.Font.Bold = True
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
' ====================================================================================
' CLEANUP RAW LOGS CLEANUP RAW LOGS CLEANUP RAW LOGS CLEANUP RAW LOGS CLEANUP RAW LOGS
' ====================================================================================
    Range("A1:O" & numrowsRaw).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.RowHeight = 15
        Cells.Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A1").Select
    Range("A1:O" & numrowsRaw).Select
    Selection.Columns.AutoFit
    Range("A1").Select
' =========================================================================================
' VULN RANK VULN RANK VULN RANK VULN RANK VULN RANK VULN RANK VULN RANK VULN RANK VULN RANK
' =========================================================================================
    Range("C2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    Columns("B:B").Select
    Selection.Cut
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight
    Range("A1").Select
    Sheets("vulnerabilities").Name = "Raw Logs"
    Range("A1:L" & numrowsRaw).Select
    
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "'Raw Logs'!R1C1:R" & numrowsRaw & "C6", Version:=xlPivotTableVersion14).CreatePivotTable TableDestination _
        :="", TableName:="Vuln Rank", DefaultVersion:= _
        xlPivotTableVersion14
    ActiveSheet.PivotTables("Vuln Rank").AddDataField ActiveSheet.PivotTables( _
        "Vuln Rank").PivotFields("Vulnerability Name"), "Count of Vulnerability Name", _
        xlCount
    With ActiveSheet.PivotTables("Vuln Rank").PivotFields("Vulnerability Name")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("Vuln Rank").PivotFields("Vulnerability Name").AutoSort _
        xlDescending, "Count of Vulnerability Name"
' ===================================================================================================
' VULN RANK NEW SHEET VULN RANK NEW SHEET VULN RANK NEW SHEET VULN RANK NEW SHEET VULN RANK NEW SHEET
' ===================================================================================================
    numrowsRawPivot = ActiveSheet.UsedRange.Rows.Count
    Range("A1:B" & numrowsRawPivot).Select
    Selection.Copy
    firstSheet = ActiveSheet.Name
    Sheets.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Vulnerability Name"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Count"
    Range("A1:B1").Select
    Selection.AutoFilter
    Selection.Font.Bold = True
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    Range("A1:B" & numrowsRawPivot).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    
    secondSheet = ActiveSheet.Name
    
    ActiveWorkbook.Worksheets("Sheet2").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet2").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("B1"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet2").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Rows("2:2").Select
    Selection.Delete Shift:=xlUp
    Range("A1:B" & numrowsRawPivot).Select
    Selection.Columns.AutoFit
    Range("A1").Select
    Sheets("Sheet2").Name = "Vuln Rank"
    Sheets("Sheet1").Select
    ActiveWindow.SelectedSheets.Delete
    Sheets("Raw Logs").Select
' ===================================================================================================
' HOST RANK HOST RANK HOST RANK HOST RANK HOST RANK HOST RANK HOST RANK HOST RANK HOST RANK HOST RANK
' ===================================================================================================
    Range("A1:L" & numrowsRaw).Select
    Selection.AutoFilter
    Selection.AutoFilter
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "'Raw Logs'!R1C1:R" & numrowsRaw & "C6", Version:=xlPivotTableVersion14).CreatePivotTable TableDestination _
        :="", TableName:="Host Rank", DefaultVersion:= _
        xlPivotTableVersion14
    ActiveSheet.PivotTables("Host Rank").AddDataField ActiveSheet.PivotTables( _
        "Host Rank").PivotFields("Merged Hostnames"), "Count of Merged Hostnames", _
        xlCount
    With ActiveSheet.PivotTables("Host Rank").PivotFields("Merged Hostnames")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("Host Rank").PivotFields("Merged Hostnames").AutoSort _
        xlDescending, "Count of Merged Hostnames"
' ===================================================================================================
' HOST RANK NEW SHEET HOST RANK NEW SHEET HOST RANK NEW SHEET HOST RANK NEW SHEET HOST RANK NEW SHEET
' ===================================================================================================
    numrowsRawPivot2 = ActiveSheet.UsedRange.Rows.Count
    Range("A1:B" & numrowsRawPivot2).Select
    Selection.Copy
    Sheets.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Merged Hostnames"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Count"
    Range("A1:B1").Select
    Selection.Font.Bold = True
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    Range("A1:B" & numrowsRawPivot2).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("Sheet4").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet4").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("B1:B" & numrowsRawPivot2), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet4").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Rows("2:2").Select
    Selection.Delete Shift:=xlUp
    Range("A1:B" & numrowsRawPivot2).Select
    Selection.Columns.AutoFit
    Range("A1").Select
    Sheets("Sheet4").Name = "Host Rank"
    Sheets("Sheet3").Select
    ActiveWindow.SelectedSheets.Delete
    Range("A1").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("A1").Select
' ===========================================================================================================
' SUMMARY TAB SUMMARY TAB SUMMARY TAB SUMMARY TAB SUMMARY TAB SUMMARY TAB SUMMARY TAB SUMMARY TAB SUMMARY TAB
' ===========================================================================================================
    Sheets.Add
    Sheets("Sheet5").Name = "Summary"
    Sheets("Summary").Select
    Sheets("Summary").Move Before:=Sheets(1)
    ActiveCell.FormulaR1C1 = "Host Rank"
    Range("A2").Select
    Sheets("Host Rank").Select
    Range("A1:B11").Select
    Selection.Copy
    Sheets("Summary").Select
    Range("A2").Select
    ActiveSheet.Paste
    Range("A14").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Vuln Rank"
    Range("A15").Select
    Sheets("Vuln Rank").Select
    Range("A1:B11").Select
    Selection.Copy
    Sheets("Summary").Select
    Range("A15").Select
    ActiveSheet.Paste
    Range("A1,A14").Select
    Range("A14").Activate
    Application.CutCopyMode = False
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.Font.Bold = True
    Cells.Select
    Selection.Columns.AutoFit
    Range("A1").Select
    Sheets("Vuln Rank").Select
    Range("A1").Select
    Sheets("Host Rank").Select
    Range("A1").Select
    Sheets("Summary").Select
    Range("A1").Select
' ===============================================================================================================
' OS RANK OS RANK OS RANK OS RANK OS RANK OS RANK OS RANK OS RANK OS RANK OS RANK OS RANK OS RANK OS RANK OS RANK
' ===============================================================================================================
    Sheets("Raw Logs").Select
    Range("A1:L" & numrowsRaw).Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "'Raw Logs'!R1C1:R" & numrowsRaw & "C12", Version:=xlPivotTableVersion14). _
        CreatePivotTable TableDestination:="", TableName:="OS Rank", _
        DefaultVersion:=xlPivotTableVersion14
    ActiveSheet.PivotTables("OS Rank").AddDataField ActiveSheet.PivotTables( _
        "OS Rank").PivotFields("Asset OS Family"), "Count of Asset OS Family", _
        xlCount
    With ActiveSheet.PivotTables("OS Rank").PivotFields("Asset OS Family")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("OS Rank").PivotFields("Asset OS Family").AutoSort _
        xlDescending, "Count of Asset OS Family"
    numrowsRaw3 = ActiveSheet.UsedRange.Rows.Count
    Range("A1:B" & numrowsRaw3).Select
    Selection.Copy
    Sheets.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.AutoFilter
    Range("A1:B1").Select
    Selection.Font.Bold = True
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    Range("A1:B" & numrowsRaw3).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "OS Version"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Count"
    Range("A1:B" & numrowsRaw3).Select
    Range("A2").Activate
    Selection.Columns.AutoFit
    Range("A1").Select
    ActiveWorkbook.Worksheets("Sheet7").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet7").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("B1:B" & numrowsRaw3), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet7").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Rows("2:2").Select
    Selection.Delete Shift:=xlUp
    Range("D2").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlColumnClustered
    numrowsRaw3 = ActiveSheet.UsedRange.Rows.Count - 1
    ActiveChart.SetSourceData Source:=Range("A1:B" & numrowsRaw3)
    ActiveSheet.Shapes("Chart 1").IncrementLeft -203.25
    ActiveSheet.Shapes("Chart 1").IncrementTop -193.5
    ActiveSheet.Shapes("Chart 1").ScaleWidth 1.95, msoFalse, msoScaleFromTopLeft
    ActiveSheet.Shapes("Chart 1").ScaleHeight 2.3645833333, msoFalse, _
        msoScaleFromTopLeft
    ActiveChart.ChartTitle.Select
    ActiveChart.ChartTitle.Text = "OS Version Count"
    Selection.Format.TextFrame2.TextRange.Characters.Text = "OS Version Count"
    With Selection.Format.TextFrame2.TextRange.Characters(1, 16).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(1, 16).Font
        .BaselineOffset = 0
        .Bold = msoTrue
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(0, 0, 0)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 18
        .Italic = msoFalse
        .Kerning = 12
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
        .Strike = msoNoStrike
    End With
    ActiveChart.Legend.Select
    Selection.Delete
    Range("A1").Select
    Sheets("Sheet7").Select
    Sheets("Sheet7").Name = "OS Rank"
    Sheets("Sheet6").Select
    ActiveWindow.SelectedSheets.Delete
    Sheets("Raw Logs").Select
    Range("A1").Select
    Sheets("Summary").Select
' ===========================================================================================================
' ADD OS RANK TO SUMMARY TAB ADD OS RANK TO SUMMARY TAB ADD OS RANK TO SUMMARY TAB ADD OS RANK TO SUMMARY TAB
' ===========================================================================================================
    Sheets("Vuln Rank").Select
    ActiveSheet.Range("$A$1:$B$" & numrowsRawPivot).AutoFilter Field:=1, Criteria1:="=MS*", _
        Operator:=xlAnd
    Range("A1:B" & numrowsRawPivot).Select
    Selection.Copy
    Sheets.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1:B11").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Summary").Select
    Range("A28").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("A28:B28").Select
    Selection.Font.Bold = True
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    Range("A27").Select
    ActiveCell.FormulaR1C1 = "Top 10 Missing Microsoft Patches"
    Range("A27").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.Font.Bold = True
    Sheets("Vuln Rank").Select
    Range("A1").Select
    ActiveWorkbook.Worksheets("Vuln Rank").AutoFilter.Sort.SortFields.Clear
    ActiveSheet.ShowAllData
    Range("A1").Select
    Sheets("Sheet8").Select
    ActiveWindow.SelectedSheets.Delete
    Sheets("Summary").Select
    Cells.Select
    Selection.Columns.AutoFit
    Range("A1").Select
    Sheets("Raw Logs").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.AutoFilter
    Selection.AutoFilter
    Range("A1").Select
    Sheets("Summary").Select
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.StatusBar = True
    
End Sub

Open in new window

0
Comment
Question by:m_travis
6 Comments
 
LVL 7

Assisted Solution

by:slubek
slubek earned 167 total points
ID: 40411737
I see that your macro is result of Macro Recorder. It is very hard to analyze such huge subroutines (651 lines), so my first advice is to break it down into functional pieces. That could shorten your macro.
Next, find codes like:
    Range("A1").Select
    Range("C2").Select

Open in new window

(lines 56,57) and think of leaving only last one.
Codes like
    Range("C2:C" & numrowsRaw).Select
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "IP Address"

Open in new window

(lines 62-64) are also ineffective, because of not used commands from lines 62,63.

Next time, when you want to record macro, first try all operations to avoid recording unnecessary steps.
0
 
LVL 1

Author Comment

by:m_travis
ID: 40411772
Sorry about that. this was a recorded marco from a coworker that I modified and didn't even think about going through looking for dead code. I will definite go through it to minimize the dead code.
0
 
LVL 45

Assisted Solution

by:Martin Liss
Martin Liss earned 166 total points
ID: 40411861
One general thing you can do that will speed it up is to avoid "Select" wherever possible so instead of

Columns("B:B").Select
    Selection.Insert Shift:=xlToRight

Open in new window

you can  do

Columns("B:B").Insert Shift:=xlToRight

Open in new window


and instead of

Range("C1").Select
    ActiveCell.FormulaR1C1 = "Merged Hostnames"

Open in new window

you can do
Range("C1").FormulaR1C1 = "Merged Hostnames"

Open in new window

etc., etc.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 27

Accepted Solution

by:
Glenn Ray earned 167 total points
ID: 40412048
You can replace lines 84-119 & 196-231 & 300-335 (and so on) with this small section of code:
    Dim x As Integer
    For x = xlEdgeLeft To xlInsideHorizontal '7 to 12
    With Selection.Borders(x)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Next x

Open in new window

0
 
LVL 3

Expert Comment

by:byronwall
ID: 40412107
You may get a good bang for your buck by delaying calculations to the point where they are needed.  I assume that this macro is running with the Caluclation in Automatic since I don't see it changed anywhere.

At the top of the code, you can add:

Application.Calculation = xlCalculationManual

Open in new window


This will prevent Excel from performing recalculations when cell values change.  Since you are adding a large number of formulas, this may improve performance significantly.

If you reach a spot where the calculation is needed you can then call
Application.Calculate

Open in new window


to force a recalculation.

You may find that you don't need to actually do this until the end of the code's execution.  It is a little hard to guess exactly what all is being done in the macro, but I don't see much conditional logic or other items that would require calculation while the VBA runs.  Depending on what all those PivotTable calls are doing, you may need a recalculation before they are created.  You may also get away without it and then simply refresh the PivotTables at the end.

Once you are done, be sure to set the mode back to Automatic since it will not revert on its own:
Application.Calculation = xlCalculationAutomatic

Open in new window


I have used this technique on all sorts of VBA work and it will drastically improve performance when you are creating large amounts of formulas.

This setting is the same as changing the option in the Formulas ribbon -> Calculation Options -> Manual.  You can also change it there and run the VBA instead of adding the code if you want to test its effectiveness.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40441274
I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
A short article about a problem I had getting the GPS LocationListener working.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now