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…
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
Solution – Challenge 30 – Average Last 5 Numbers in a Range
Below is a possible solution to Challenge 30 – Average Last 5 Numbers in a Range. Use below formula – =IFERROR(AVERAGE(INDIRECT("A"&LARGE(INDEX(ISNUMBER(A1:A100)* (ROW(A1:A100)),,),MIN(COUNT(A1:A100),5))&":"&"A"&LARGE( INDEX(ISNUMBER(A1:A100)*(ROW(A1:A100)),,),1))),0) The workbook containing this solution is uploaded to Solution – Challenge 30 – Average Last 5 Numbers in a Range
Challenge 32 – Convert Matrix into Linear Column – I
Suppose you have been given a grid like below. The challenge before you is to write a formula to create a linear column with no blanks in between. The column ranges would be A to D whereas row ranges would be from 1 to 100. Hence, your formula has to be flexible enough to consider…
Article 32 – Calculate Working Hours between Two Ranges and Exclude Weekends and Holidays (SLA Calculation)
Suppose you have two time stamps say A1: 23-Dec-2015 09:15 AM and B1: 29-Dec-2015 02:30 PM and say your working hours are between 09:00 AM to 05:00 PM. You have been tasked to calculate the working hours between these two dates and you need to exclude weekends (here – 26-Dec-15 and 27-Dec-15) and any holiday…
Excel Quiz 27
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…
Solution – Challenge 29 – Reverse (Flip) a Number String
Below is a possible solution to the challenge – Challenge 29 – Reverse (Flip) a Number String You may use below formula to reverse a number string – =SUMPRODUCT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1) *10^ROW(INDIRECT("1:"&LEN(A1)))/10)
Article 31 – Slab Billing – Calculate Income Tax, Electricity (Utility) Bills based on Slabs
You will encounter slab billings in two very common documents – One is Electricity / Utility and another one is Income Tax. If you see your electricity bills, you will notice following type of entries (values are for illustration purposes only, please do not attach any meaning to them) 0 – 50 Units – $1.5…