In VBA, often, we have situations where we need to check if a range is blank or not. The popular formula which we see following is used to check whether range is blank or not. WorksheetFunction.CountA(Range("A1:A100")) Sub CheckifBlank() If WorksheetFunction.CountA(Range("A1:A100")) Then MsgBox "Range is Not Blank" Else MsgBox "Range is Blank" End If End Sub…
Category: Tips and Tricks
Tips & Tricks 123 – Change the Color of Border Lines of Active Cell
Notice the color of Active Cell which is Green. Few people may like some other color. Excel doesn't provide a native facility to change this. But Chip Pearson offers an Add-in which provides this functionality and has many custom options also. http://www.cpearson.com/excel/RowLiner.htm
Tips & Tricks 122 – Always Open a Specific Worksheet when Workbook is Opened
This can only be accomplished through VBA. 1. Save your file as .xlsm 2. ALT+F11 3. Locate your Workbook name in Project Explorer Window 4. Double click on ThisWorkbook 5. Put following code in ThisWorkbook (Replace Sheet Name appropriately) Private Sub Workbook_Open() Worksheets("Sheet Name").Activate End Sub
Tips & Tricks 121 – Macro to Protect / Un-protect All or Selective Sheets
There may be scenarios where you want the facility to protect or unprotect all sheets in one go with added option to choose sheets where to perform this operation. Below is the code to perform this. 1. Save your file as .xlsm 2. ALT+F11 3. Locate your Workbook name in Project Explorer Window 4. Right…
Tips & Tricks 120 – Filter by Color Drop Down Menu Greyed Out Problem
Sometimes, you find that though your are having colored cells but Filter by Color is greyed out i.e. it can not be selected. Following are the possible causes – 1. Your sheets may be grouped. If they are grouped, you will notice word "Group" in the title bar of your Excel workbook. Another way to…
Tips & Tricks 119 – Numerology Sum of the Digits aka Sum the Digits till the result is a single digit
In Numerology, it is often a task to add the digits till the result is a single digit. For example, 74 = 7 + 4 = 11 = 1 + 1 = 2 23 = 2 + 3 = 5 78 = 7 + 8 = 15 = 1 + 5 = 6 1234567 =…
Tips & Tricks 118 – Stop Auto Creation to Hyperlinks
We all know that if you enter a web address like www.microsoft.com, http://www.microsoft.com or even e mail address like admin@eforexcel.com, Excel converts these to Hyperlinks which can be clicked. Sometimes, we don't want this behaviour and we want to stop this – Trick 1 – After entering a web address / e mail address, press…
Tips & Tricks 117 – VBA – How to Count a particular character in a String
When we use formulas inside Excel, we have following stock formula to count the number of times a character appears in a string – =LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),"a","")) In this, we are trying to count the occurrence of character "a" in A1. Let's assume that A1 = "Abraham Arthurway". Hence, the answer which we would get will be…
Tips & Tricks 116 – Count the Number of Words in a Cell / Range
Suppose you have been given the following and you need to count the number of words in a cell or in a range. Formula for calculating number of words in a cell – =LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+(TRIM(A1)<>"") Formula for calculating number of words in a range – =SUMPRODUCT(LEN(TRIM(A1:A100))-LEN(SUBSTITUTE(TRIM(A1:A100)," ",""))+(TRIM(A1:A100)<>""))
Tips & Tricks 115 – Insert Fixed Current Date and Current Time
We all are aware about today() and now() formulas which insert current date and current date/timestamp. But these change with every recalculation of your worksheet. But if you want to enter the current date and time which doesn't change with recalculation i.e. it gets fixed, then following Excel shortcuts can be used. Current Date –…