Excel VBA – Delete row based on content

This is a function to search with specific content, i.e. in a header, then delete the whole row in Excel.

If you wish to delete that row with specific content, you can use the phrase in green part, it will only delete that specific row instead.

Function DeleteRowWithContents(ByVal xContent As String, ByVal validColumn As String)

Dim i, Last As Long

Last = Cells(Rows.Count, validColumn).End(xlUp).Row
For i = Last To 2 Step -1
If (Cells(i, validColumn).Value) = xContent Then
‘Cells(i, “A”).EntireRow.ClearContents ‘ USE THIS TO CLEAR CONTENTS BUT NOT DELETE ROW
Cells(i, validColumn).EntireRow.Delete
End If
Next i

End Function


Excel VBA – find out the position of your last row, column & column name…

This is a very useful function to me, it helps to find out the position of the last row and column in your worksheet, and will also get the column name for calculation and automation.  It is also hard to find out and fix the boundary in a worksheet when you are performing automatic calculation and move.

You can refer to my previous “User defined type”, to create your own variable first, so that this function will work properly.


‘ Find out the LAST row, column, and return the values (row, column & column name)

Function FindLastRowColumn() As RowColumn

Dim infoRowColumn As RowColumn
Dim iCol As Integer
Dim iRow As Long
Dim lastColumn As Integer
Dim lastRow As Long
Dim MBox As Integer

iCol = ActiveSheet.UsedRange.Column – 1 + ActiveSheet.UsedRange.Columns.Count
lastColumn = iCol

iRow = ActiveSheet.UsedRange.Row – 1 + ActiveSheet.UsedRange.Rows.Count
lastRow = iRow

ColumnName = ConvertToLetter(lastColumn)
‘MBox = MsgBox(“Column: ” & LastColumn & “(” & ColumnName & “)” & vbCrLf & “Row: ” & LastRow, vbOKOnly)

infoRowColumn.xRow = lastRow
infoRowColumn.xColumn = lastColumn
infoRowColumn.xColumnName = ColumnName

FindLastRowColumn = infoRowColumn

End Function


Excel VBA – Check if your worksheet exists…

This is a function to check if the worksheet you are looking or working exists.

Function IsWorksheetExist(ByVal WSName As String) As Boolean

Dim CheckWS As Worksheet

IsWorksheetExist = False

For Each CheckWS In ActiveWorkbook.Worksheets
If CheckWS.Name = WSName Then IsWorksheetExist = True
Next CheckWS

End Function

Excel VBA – delete a single column…

This is a function to delete a specific column based on the Column letter and Row number.

e.g. DeleteSingleColumn(“A”, 1), then Column A will be completely deleted

Function DeleteSingleColumn(ByVal validColumn As String, ByVal StartRow As Long)

Dim i As Long
Dim DelString As String

DelString = validColumn & StartRow


End Function

Excel VBA – Check if workbook exists…

This is a function to verify if your workbook exists in the defined location, so that before you can open your workbook, VBA codes can open your desired workbook properly.

It will return “TRUE” if the workbook is located in the desired location.

It will return “FALSE” if the workbook doesn’t exist in the desired location.

Function DoesWorkBookExist(ByVal wbPath As String, ByVal WBName As String) As Boolean

Dim i As Integer
Dim FileCheck As String

FileCheck = wbPath & “\” & WBName

If Dir(FileCheck) <> “” Then
‘MsgBox (“Workbook – ” & wbName & ” exists”)
DoesWorkBookExist = True
‘MsgBox (“Workbook – ” & wbName & ” doesn’t exist”)
DoesWorkBookExist = False
End If

End Function

Excel VBA – Check if your workbook exist or not?

Before opening your workbook, you will need to verify if the workbook exists or not, so that it won’t return error or the VBA will stop improperly.

If the workbook exists, it will return “TRUE“.

If the workbook doesn’t exist, it will return “FALSE“.

Function DoesWorkBookExist(ByVal wbPath As String, ByVal WBName As String) As Boolean

Dim i As Integer
Dim FileCheck As String

FileCheck = wbPath & “\” & WBName

If Dir(FileCheck) <> “” Then
‘MsgBox (“Workbook – ” & wbName & ” exists”)
DoesWorkBookExist = True
‘MsgBox (“Workbook – ” & wbName & ” doesn’t exist”)
DoesWorkBookExist = False
End If

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, _

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

VBA – Visual Basic for Application

Visual Basic for Application (VBA), is an event-driven programming by Microsoft, the original programming is based on Visual Basic 6, which is an Integrated Development Environment (IDE).

It begins in Microsoft Office 97 and allows users to create automation via Macro recorder or VBA programming; this is a simple straight forward Basic style programming language and replace the older scripts in older Office 95 or earlier.

The VBA had been upgraded to version 7 in Microsoft Office 2010, and it provides more functions and options to perform automation in Microsoft Office – Word, Excel, PowerPoint, Visio, Project and Access.  Since Microsoft Office 2011 for Mac, VBA had been re-added, Mac users can also enjoy the same automation, but with limited developer interface than in Office on Windows platform, i.e. the path will be different in Mac, to use “:” as the directory.

Automation in Microsoft Office application is an important feature, that can help users to reduce tedious, repeatable tasks when working in Office applications, in addition, to reduce human errors in handling large amount of data.  However, this VBA can only work within the Office Applications environment.