Option Explicit

Author: Keith

  • DeleteBottomRowsFromWorksheet

    —

    by

    Keith
    in , ,

    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…

  • GetLastUsedColumnNumberInWorksheet

    —

    by

    Keith
    in , ,

    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 = _…

  • GetLastUsedRowNumberInWorksheet

    —

    by

    Keith
    in , ,

    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 = _…

  • DeleteAllConnectionsFromWorkbookOfType

    —

    by

    Keith
    in , ,

    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

    —

    by

    Keith
    in , ,

    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

    —

    by

    Keith
    in , ,

    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

  • WorkbookSheetExists

    —

    by

    Keith
    in

    Public Function WorkbookSheetExists( _ wb As Workbook, _ strSheetName As String _ ) As Boolean Dim ws As Worksheet WorkbookSheetExists = False For Each ws In wb.Sheets If ws.Name = strSheetName Then WorkbookSheetExists = True End If Next ws End Function

  • FolderExists

    —

    by

    Keith
    in , ,

    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

    —

    by

    Keith
    in , ,

    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

  • GetFilePath

    —

    by

    Keith
    in , , ,

    Public Function GetFilePath( _ sFullFileNameWithPath As String _ ) As String Dim fs As Object Set fs = CreateObject(“Scripting.FileSystemObject”) GetFilePath = _ fs.GetAbsolutePathName( _ sFullFileNameWithPath _ ) End Function