
ReturnName
Function ReturnName(ByVal num As Integer) As String ReturnName = Split(Cells(, num).Address, "$")(1) End Function
Function ReturnName(ByVal num As Integer) As String ReturnName = Split(Cells(, num).Address, "$")(1) End Function
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
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
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
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
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
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
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
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
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