Suppose, you have data like this and you want to delete rows 3,4,6,8,11 & 12 as they are blanks. You need not loop through all cells but you can perform this operation is one shot. You can use below code for the same On Error Resume Next Range("A1:A13").SpecialCells(xlBlanks).EntireRow.Delete On Error GoTo 0
Solution – Challenge 41 – Sum the Maximum Number where duplicates Exist
Below is a possible solution to the challenge Challenge 41 – Sum the Maximum Number where duplicates Exist Enter the below formula as Array Formula i.e. not by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put…
Challenge 43 – Consolidate Daily Sheets into a Weekly Sheet
This time, challenge is going to be tougher. An user receives a daily sheet which has got project status and you need to prepare a consolidated worksheet for the week everyday. 1. Your sheets are named Day1, Day2…..Day7. 2. One sheet can have a maximum of 20 entries. 3. The entries may not be same…
Article 39 – Sorting in Excel
First thing first – Excel doesn’t sort as per ASCII character codes which we generally expect. It has its own sorting logic which is detailed below for Ascending sort (reverse the same for Descending sort). The order followed in an Ascending sort is Number > Text > Alphanumeric text > Logical values > Error values…
Excel Quiz 32
Downloads 03 – Template 03 – A Flexible Agile Burndown Chart
The template can be download from – Template 03 – Agile Burndown Chart Template Unprotected version of template – Template 03 – Agile Burndown Chart Template_Unprotected I decided to create a template for a Burndown Chart in Excel which is very highly used in Agile Project Management. Next download template will be about Burnup Chart…
Tips & Tricks 136 – Quickly Copy a Formula or a Value in a Large Number of Cells
Say, you have written a formula (or put a value) in cell B1 and you want to fill in this formula till B10000. You can drag the formula but higher the number of cells, more tedious it becomes. Fortunately, we have a way to do this very fast. 1. Put formula in B1. 2. CTRL+C…
Solution – Challenge 40 – Cryptography Challenge 2 – Fully Functional Caesar’s Shift Cipher
Below is a possible solution to the challenge – Challenge 40 – Cryptography Challenge 2 – Fully Functional Caesar’s Shift Cipher 1. Make a backup of your workbook 2. Open your workbook and ALT+F11 3. Locate your Workbook name in Project Explorer Window 4. Right click on your workbook name > Insert > Module 5….
Challenge 42 – Split a Sentence Into Words in Different Cells
This time the challenge before you is to write a formula that if a sentence is given in A1, your formula if dragged to the right should split the sentence into words. Remember, it has to be a single formula which should be put into B1 and dragged to the right. See the examples below…
Tips & Tricks 135 – I input (c) or (C) and it gets converted to Copyright Symbol..I don't want this
There are many ways to overcome this. 1. a. Put following in a cell and press Enter c) b. Go back to the cell and type first bracket to complete (c) 2. a. You can type (c) preceded with some special character and press enter. b. Go back to that cell and delete that special…