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

 

 

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

End Function

Lights show in Hong Kong Flower Show 香港花卉展覽燈光 2017


Themed “Blossoms of Love”, the Hong Kong Flower Show 2017 showcases the rose as its spotlight flower and Hong Kong as a beautiful city of love. The installation will combine lighting effects, 3D effects and multi-media.

今年香港花卉展覽主題為「愛‧賞花」, 並以美麗可愛的玫瑰為主題花,展現香港是一個美麗充滿愛的城市。為配合大會的主題,展品運用燈光效果、互動元素、三維(立體)藝術效果及多媒體以配合展品,以增加花展的吸引力。

Date: 16 Mar 2017

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