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
Category: VBA
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 40 – Cryptography Challenge 2 – Fully Functional Caesar’s Shift Cipher
We made ourselves familiarized with Caesar's Shift in last challenge – Challenge 39 – Cryptography Challenge 1 – Caesar's Shift Cipher That challenge was for writing a formula to perform Caesar's Shift on a single alphabet. But now, we need to have a fully functional Caesar's Shift Cipher. The challenge before you is to write…
Article 37 – VBA – Generating Unique (Non-repeating) Random Numbers Efficiently
This article is about how to generate unique or non-repeating random numbers given two limits. Hence, if lower limit is 10 and maximum limit is 100, hence, it should generate random numbers between 10 and 100. The algorithm to ensure uniqueness is following – 1. Given lower limit and upper limit, generate all numbers and…
Article 36 – VBA – User Names in Excel
Many times, we are required to fetch User Names in Excel. Unfortunately, getting User Names in Excel is possible only through VBA. These are very small pieces of VBA codes and even a person not knowing VBA can make use of them by following the instructions here. I am going to discuss the various type…
Article 35 – VBA – Timer Function and Overcoming Midnight Limitation of Timer Function
The basic and classic and most popular mode to time your code is achieved through Timer function which is natively supplied by VBA. It returns a Single representing the number of seconds elapsed since midnight. Hence, your code will look like following when you want to measure the time and it gives the result in…
Tips & Tricks 124 – VBA – Check if a Range is Blank When Range Contains Formulas returning Blanks
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 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…
Article 30 – VBA – Approaches for Unique Count and Time Performance Results for the Same
As part of this article, we will look into various approaches for counting unique in VBA and also see what time do they take to determine the best approach on the basis of "Time Taken". I have used Charles William's MicroTimer for timing the time. https://msdn.microsoft.com/en-us/library/aa730921%28v=office.12%29.aspx We will see performance of these approaches with following…