Excel VBA – delete all blank columns without any content

This is a function to identify when the column(s) doesn’t have any content in each cell, then delete that entire column(s).  It is a amazing function to clean up your spreadsheets before modifying your data.

Function DeleteBlankColumns()
Dim Col As Long, ColCnt As Long, rng As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

On Error GoTo Exits:

If Selection.Columns.Count > 1 Then
Set rng = Selection
Else
Set rng = Range(Columns(1), Columns(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column()))
End If
ColCnt = 0
For Col = rng.Columns.Count To 1 Step -1
If Application.WorksheetFunction.CountA(rng.Columns(Col).EntireColumn) = 0 Then
rng.Columns(Col).EntireColumn.Delete
ColCnt = ColCnt + 1
End If
Next Col

Exits:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Function

Advertisements

Excel VBA – delete all blank rows without any content

This is a function to identify when the row(s) doesn’t have any content in each cell, then delete that entire row(s).  It is a amazing function to clean up your spreadsheets before modifying your data.

Function DeleteBlankRows()
Dim Rw As Long, RwCnt As Long, rng As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

On Error GoTo Exits:

If Selection.Rows.Count > 1 Then
Set rng = Selection
Else
Set rng = Range(Rows(1), Rows(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row()))
End If
RwCnt = 0
For Rw = rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(rng.Rows(Rw).EntireRow) = 0 Then
rng.Rows(Rw).EntireRow.Delete
RwCnt = RwCnt + 1
End If
Next Rw

Exits:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Function

Excel VBA – check if file or directory exists?

This is a function to verify if the desired file or directory exist, it is also important to get the status before any file operation being performed.

If the file or directory exists, it will return TRUE, otherwise, will return FALSE.

Function FileOrDirExists(ByVal PathName As String) As Boolean
‘Macro Purpose: Function returns TRUE if the specified file
     ‘               or folder exists, false if not.
     ‘PathName     : Supports Windows mapped drives or UNC
     ‘             : Supports Macintosh paths
     ‘File usage   : Provide full file path and extension
     ‘Folder usage : Provide full folder path
     ‘               Accepts with/without trailing “\” (Windows)
     ‘               Accepts with/without trailing “:” (Macintosh)

Dim iTemp As Integer

    ‘Ignore errors to allow for error evaluation
    On Error Resume Next
iTemp = GetAttr(PathName)

  ‘Check if error exists and set response appropriately
Select Case Err.Number
Case Is = 0
FileOrDirExists = True
   Case Else
FileOrDirExists = False
  End Select

   ‘Resume error checking
On Error GoTo 0
End Function

Excel VBA – Text to Columns feature

It is a subroutine and can be executed directly via the Macro list.  The function is to modify delimited text into separate columns, so you can manipulate data better and easily.

objRange1 is the definition of the range within a worksheet, you can specify.

Destination:=Range(“A3”) is the location where the output will be appeared, you can specify.

Sub Split2Col()

Dim objRange1 As Range

‘Set up the ranges
Set objRange1 = Range(“A1:A20”)

‘Do the first parse
objRange1.TextToColumns _
Destination:=Range(“A3”), _
DataType:=xlDelimited, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=True, _
OtherChar:=”|”

End Sub

Excel VBA – How to create User-Defined Type…

You can create your own User-Defined Type in VBA, which you can group the common variables in this type “RowColumn”.

In this “RowColumn” variable, it will contain Row, Column Number, Column Letter and the Address (range reference for the specified range, this can speed up in calculation).

Now you will be able to define your own “RowColumn” as variable,

e.g. Dim infoRowColumn As RowColumn

Option Explicit

Public Type RowColumn
xRow As Long
xColumn As Integer
xColumnName As String
xAddress As String
End Type

Excel VBA – Check if Workbook is opened?

This is a function, to check if your workbook is opened or not opened.  If the workbook is opened, it will return the “True” value for further work; if the workbook is not opened, it will return “False”.  You can perform some basic file operation and validation when working with multiple workbooks or Excel spreadsheets.

Function IsWorkBookOpen(ByVal WBName As String) As Boolean

Dim WBook As Workbook

On Error Resume Next
Set WBook = Workbooks(WBName)
If WBook Is Nothing Then ‘Not open
‘MsgBox wbName & “Workbook is not open”, vbCritical
Set WBook = Nothing
IsWorkBookOpen = False
Else ‘It is open
‘MsgBox wbName & ” is open”, vbInformation
Set WBook = Nothing
IsWorkBookOpen = True
End If

End Function

Excel VBA – How to convert Column Number into Column Letter?

This is a function, to convert the Column in number into the Column Letter, in order to work with most Range, functions in VBA, the Column Letter will be very useful than the Column number.

e.g. Column “A” = Column number 1

Column “C” = Column number 3

Function ConvertToLetter(ByVal iCol As Integer) As String

If iCol <= 26 Then
‘ Columns A-Z
ConvertToLetter = Chr(iCol + 64)
End If

If iCol > 26 Then
‘ Column label greater than Z, going into AA…
ConvertToLetter = Chr(Int((iCol – 1) / 26) + 64) & Chr(((iCol – 1) Mod 26) + 65)
End If

End Function

Excel VBA – How to find out workbooks’ path or location?

This is a custom function in Excel VBA, where you can find out the path or location of your workbook; it returns the information as a text output and you can make use of this for further programming,

Function DetectPath(ByVal WBName As String) As String
DetectPath = Workbooks(WBName).Path

End Function

Excel VBA – How to delete entire row(s)?

This is a custom function, to delete certain row(s) based on your specified Column and Row, then choose to delete the subsequent rows together in one go.

Function DeleteRows(ByVal validColumn As String, ByVal StartRow As Long, ByVal NumOfRows As Long)

Dim i As Long
Dim DelString As String

DelString = validColumn & StartRow & “:” & validColumn & NumOfRows

Range(DelString).EntireRow.Delete

End Function

e.g.
OpenResult = DeleteRows(“A”, 1, 9)