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.

Excel VBA – How to create User-Defined Type…

‘===================================================================================
‘ 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 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

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 – 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

Range(DelString).EntireColumn.Delete

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 – 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
Else
‘MsgBox (“Workbook – ” & wbName & ” doesn’t exist”)
DoesWorkBookExist = False
End If

End Function

Spring whisperings…

Upon reaching the Sakuranomiya JR (Osaka) station, the reports that the cherry blossoms (Sakura) were appearing slightly later than average this year proved quite true.  However, pleasantly, my visit was not greeted with complete disappointment. While strolling through the Sakura Kema Sakuranomiya Park (毛馬桜之宮公園), I was elated when I spotted .. faintly .. here and there … a few peeping cherry blossoms, shyly unfolding their translucent pinkish wings … quietly whispering the beginning of the Sakura season .. for the Osaka region ..

With renewed determination … I quickened my steps and finally … right before me … in one end of the park, a lone sakura tree blooming prettily ….. and catching quite a few passersby’s admiration and smiling photo snaps indeed !! 

Date: 29 Mar 2017

Hong Kong International A Cappella Festival 2017

2017 HKFYG Jockey Club Hong Kong International a cappella Festival

International a cappella Festival brings harmonious sounds to the community

Since 2011, The Hong Kong Jockey Club Charities Trust has been supporting the “HKFYG Jockey Club Hong Kong International a cappella Festival”, which uses the power of the human voice to develop the creative potential of young people. Now in its seventh year, this annual music event has been expanded to include the wider community. A new addition this year has been the “a cappella camp”, which gives young people of different musical backgrounds the rare opportunity for mutual exchange and sharing. Don’t miss these incredible a cappella performances – mark the date!

2017香港青年協會賽馬會香港國際無伴奏合唱節
國際無伴奏合唱節　將動人和聲傳遍社區

香港賽馬會慈善信託基金自2011年起，連續七年支持「香港青年協會賽馬會香港國際無伴奏合唱節」，以音樂激發青年人的創作潛能。今年無伴奏合唱節繼續將獨特音樂推廣至社區，更新增「無伴奏合唱營」，給予不同音樂背景的青年人互相交流及分享的難得機會。萬勿錯過連場精采的無伴奏合唱表演，立即記下活動日子！

Date: 24 Mar 2017