Option Explicit

Tag: Excel

  • 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

    , ,

  • Why Use VBA in Excel?

    Excel VBA, or Visual Basic for Applications, is a programming language that allows developers to automate tasks and build applications within the Excel environment. Utilizing Excel VBA can dramatically improve your productivity and increase your Excel skillset, as well as provide you with the opportunity to create customized macros for your business’ specific needs. Here…

    ,

  • 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

    ,

  • WorkbookSheetExists

    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

    , ,