Option Explicit

Code

  • ReturnName

    Function ReturnName(ByVal num As Integer) As String
  ReturnName = Split(Cells(, num).Address, "$")(1)
End Function

  • DeleteInitialColumnsFromWorksheet

    Note: This function depends on ReturnName

    Sub DeleteInitialColumnsFromWorksheet( _
    ws As Worksheet, _
    NumberOfColumns As Integer _
)
    With ws
        .Columns( _
            ReturnName( _
                1 _
            ) & _
            ":" & _
            ReturnName( _
                NumberOfColumns _
            ) _
        ).Delete _
            Shift:=xlToLeft
    End With
End Sub

  • DeleteBottomRowsFromWorksheet

    Note: This method depends on GetLastUsedRowNumberInWorksheet

    Sub DeleteBottomRowsFromWorksheet( _
    ws As Worksheet _
)
    Dim _
        strStartingEmptyRow As String, _
        rngStartingRow As Range, _
        rngEmptyRows As Range, _
        lngLastRow As Long
    lngLastRow = _
        GetLastUsedRowNumberInWorksheet( _
            ws _
        )
    If lngLastRow = _
        -1 Then
        Exit Sub
    End If
    strStartingEmptyRow = _
        CStr( _
            lngLastRow + 1 _
        )
    Set rngStartingRow = _
        ws.Rows( _
            strStartingEmptyRow & _
            ":" & _
            strStartingEmptyRow _
        )
    Set rngEmptyRows = _
        Range( _
            rngStartingRow, _
            rngStartingRow.End( _
                xlDown _
            ) _
        )
    rngEmptyRows.Delete _
        shift:=xlUp
End Sub

  • GetLastUsedColumnNumberInWorksheet

    Function GetLastUsedColumnNumberInWorksheet( _
    ws As Worksheet _
) As Long
    Dim _
        rng As Range, _
        rngResults As Range
    Set rng = _
        ws.Cells
    Set rngResults = _
        rng.Find( _
            what:="*", _
            After:=rng.Cells(1), _
            Lookat:=xlPart, _
            LookIn:=xlFormulas, _
            SearchOrder:=xlByColumns, _
            SearchDirection:=xlPrevious, _
            MatchCase:=False _
        )
    If _
        rngResults Is Nothing _
        Then
        GetLastUsedColumnNumberInWorksheet = _
            -1
    Else
        GetLastUsedColumnNumberInWorksheet = _
            rngResults.Column
    End If
    Exit Function
ErrorHandler:
    GetLastUsedColumnNumberInWorksheet = _
        -1
End Function

  • GetLastUsedRowNumberInWorksheet

    Function GetLastUsedRowNumberInWorksheet( _
    ws As Worksheet _
) As Long
    Dim _
        rng As Range, _
        rngResults As Range
    Set rng = _
        ws.Cells
    Set rngResults = _
        rng.Find( _
            what:="*", _
            After:=rng.Cells(1), _
            Lookat:=xlPart, _
            LookIn:=xlFormulas, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious, _
            MatchCase:=False _
        )
    If _
        rngResults Is Nothing _
        Then
        GetLastUsedRowNumberInWorksheet = _
            -1
    Else
        GetLastUsedRowNumberInWorksheet = _
            rngResults.Row
    End If
    Exit Function
ErrorHandler:
    GetLastUsedRowNumberInWorksheet = _
        -1
End Function

  • DeleteAllConnectionsFromWorkbookOfType

    Public Function DeleteAllConnectionsFromWorkbookOfType( _
    wb As Workbook, _
    xlconnType As XlConnectionType _
)
    Dim _
        wbcn As WorkbookConnection
    For Each wbcn In wb.Connections
        If wbcn.Type = xlconnType Then
            wbcn.Delete
        End If
    Next wbcn
End Function

  • DeleteAllSlicersFromWorkbook

    Public Function DeleteAllSlicersFromWorkbook( _
    wb As Workbook _
)
    Dim _
        ws As Worksheet, _
        shp As Shape
    For Each ws In wb.Sheets
        For Each shp In ws.Shapes
            If shp.Type = msoSlicer Then shp.Delete
        Next shp
    Next ws
End Function

  • GetFileExtension

    Public Function GetFileExtension( _
    sFullFileNameWithPath As String _
) As String
    Dim fs As Object
    Set fs = CreateObject("Scripting.FileSystemObject")
    GetFileExtension = _
        fs.GetExtensionName( _
            sFullFileNameWithPath _
        )
    Set fs = Nothing
End Function

  • FolderExists

    Function FolderExists( _
    strCompleteFolderPath As String _
) As Boolean
    Dim _
        fs As Object
    Set _
        fs = _
            CreateObject( _
                "Scripting.FileSystemObject" _
            )
    FolderExists = _
        fs.FolderExists( _
            strCompleteFolderPath _
        )
    Set _
        fs = _
            Nothing
End Function

  • DeleteVBACodeFromWorkbook

    Sub DeleteVBACodeFromWorkbook( _
    wb As Workbook _
)
    Dim _
        vbc As VBComponent
    For Each vbc In wb.VBProject.VBComponents
        If vbc.Type <> vbext_ct_Document Then
            wb.VBProject.VBComponents.Remove vbc
        End If
    Next vbc
End Sub