Option Explicit

Category: snippet

  • CompressPageRanges

    Function CompressPageRanges(ByVal pageList As String) As String Dim pages() As String Dim i As Long Dim result As String Dim startPage As Long Dim endPage As Long pages = Split(pageList, “,”) For i = 0 To UBound(pages) If startPage = 0 Then startPage = CLng(pages(i)) endPage = startPage ElseIf CLng(pages(i)) = endPage + 1 Then…

    ,

  • IsCellHighlighted

    Function IsCellHighlighted(cell As Range) As Boolean IsCellHighlighted = (cell.Interior.ColorIndex <> xlNone)End Function

    , ,

  • 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…

    ,

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

    ,

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

    ,

  • 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

    , ,