Untitled

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

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…

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…

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…

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

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)…

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…

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 &…

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…

J

Mac – How to insert Emoji and Symbols?

It is quite simple to insert Emoji and Symbols in Mac OS, you can find the Emoji & Symbols almost in any applications on Mac, select from menu and choose your desired Emoji icons or symbols, then double-click or drag-n-drop it to your application(s).

Mac – How to capture your screen?

Do you know the simple method to capture your screen in Mac OS?

Click to System Preferences > Keyboard > Shortcuts > Screen Shots, and you will find the shortcut keys to perform different style of screen captures,

– Save picture directly or copy picture to clipboard with your whole screen

– Save picture directly or copy picture to clipboard with your selected areas

^ means <alt/option> key

Chinese Lion Dance for shops… 紅磡紅青堂醒獅賀商舖

https://www.youtube.com/watch?v=_s7on3M2oiQ https://www.facebook.com/儒釋道三聖寶林紅青堂-126064110806119

Chinese Lion Dance in Hunghom, Hong Kong, this is celebration to surrounding shops, restaurants, and wish them good luck and good business.

by 紅青堂醒獅

在香港，舞獅又稱醒獅，逢年過節，如天后誕辰，或是店鋪開業，都有醒獅表演的傳統。

醒獅被認為是驅邪避害的吉祥瑞物，每逢節慶，或有重大活動，必有醒獅助興，長盛不衰，歷代相傳。

Date: 11 Mar 2017