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
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
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
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
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.
Dim objRange1 As Range
‘Set up the ranges
Set objRange1 = Range(“A1:A20”)
‘Do the first parse
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)
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)
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
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
OpenResult = DeleteRows(“A”, 1, 9)