Sun 19 Jan 2020

Excel Quiz 60

By |Sunday, January 19th, 2020|Categories: Quizzes|Tags: , , , |0 Comments

A general quiz on Excel

Sat 04 Jan 2020

Article 48 - Split A Workbook into Multiple Workbooks on the basis of a Column

By |Saturday, January 04th, 2020|Categories: Articles, VBA|Tags: , , , , |0 Comments

Sometimes, we need to prepare multiple workbooks on the basis of entries in a column. It is all the more useful in organizations where you need to send files to stakeholders.  For example, in below sheet, you want to split the files on the basis of entries in column C. You may also like to prepare 3 files on the basis of entries in column D. (more…)

Sat 21 Dec 2019

Tips & Tricks 174 - Create a Desktop Shortcut for Onedrive Documents

By |Saturday, December 21st, 2019|Categories: Tips and Tricks|Tags: , , , , , , , |0 Comments

Sometimes, you might need to create a desktop shortcut to Onedrive documents. Below is a method for Excel documents which can be used for any MS Office document.

First, you would need to find the path of Excel.exe on your computer. (more…)

Sun 01 Dec 2019

Tips & Tricks 173 - Color Code for Official Color of Excel

By |Sunday, December 01st, 2019|Categories: Tips and Tricks|Tags: , , , , , , , |0 Comments

Everybody sees the dark green color of Excel.

The color code for this color is (more…)

Fri 15 Nov 2019

VBA - Delete All Blank Rows

By |Friday, November 15th, 2019|Categories: VBA|0 Comments

This macro will delete all blank rows from a sheet.

Sub Delete_Blank_Rows()
    Dim Ws As Worksheet
    Dim Path As String, Name As String
    Dim Answer, Arr, Extension
    Dim LastRow As Long, i As Long
    
    On Error GoTo ExitSub
    Set Ws = ActiveSheet
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    'If you need to create a backup copy of the workbook before deletion
    Answer = MsgBox("Do you want to create a backup of this file", vbQuestion + vbYesNoCancel, "Backup the file?")
    
    If Answer = vbCancel Then GoTo ExitSub
    
    If Answer = vbYes Then
        'Save a copy of the workbook appended with timestamp
        Path = ActiveWorkbook.FullName
        Arr = Split(Path, ".")
        Name = Arr(UBound(Arr) - 1)
        Extension = Arr(UBound(Arr))
        Name = Name & "_" & Format(Now(), "mmddyyhhmmss")
        Arr(UBound(Arr) - 1) = Name
        Name = Join(Arr, ".")
        ActiveWorkbook.SaveCopyAs Name
    End If
    
    'Get the last used row of the worksheet
    LastRow = Ws.Cells.SpecialCells(xlLastCell).Row
    
    'We need to loop from this last row to first row and delete if cell is blank
    For i = LastRow To 1 Step -1
        If WorksheetFunction.CountA(Ws.Rows(i)) = 0 Then
            Ws.Rows(i).Delete
        End If
    Next i
ExitSub:
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
End Sub



Thu 31 Oct 2019

Excel Quiz 59 - Date & Time Quiz

By |Thursday, October 31st, 2019|Categories: Quizzes|Tags: , , , , , |0 Comments

A general quiz on Excel

Tue 15 Oct 2019

Tips & Tricks 173 - VBA - OR Condition in FIND

By |Tuesday, October 15th, 2019|Categories: Tips and Tricks, VBA|Tags: , , , |0 Comments

FIND is a very powerful function in VBA but it doesn't support OR condition. Hence, if you want to find say two values "A" or "B", then you can code an array within FIND. To do OR in FIND, you will need to use following code (this is a sample code only, there can be many variations of this code) (more…)

Mon 23 Sep 2019

Tips & Tricks 172 - Data Validation for Case Sensitivity

By |Monday, September 23rd, 2019|Categories: Tips and Tricks|Tags: , , |0 Comments

There are scenarios when you want to put a data validation which is case sensitive. Hence you want USA to be entered as USA not as usa or uSA or uSA so on. You just want all capitals. Similarly, sometimes you need only small letters say maverick. Hence, not Maverick, mavericK etc. (more…)

Fri 30 Aug 2019

Excel Quiz 58

By |Friday, August 30th, 2019|Categories: Quizzes|Tags: , , , |0 Comments

Excel Quiz 58

A quiz based on Excel functions and formulas

Sun 14 Jul 2019

Tips & Tricks 171 - Calculate Next Working day if date is of Weekend / Holiday

By |Sunday, July 14th, 2019|Categories: Tips and Tricks|Tags: , , |0 Comments

Suppose you are given a date and you are asked to calculate next working day if date is of weekend. If date is a regular workday, then you should show the same date.

For example - 8-Mar-19 is a working day. Hence, you should show the same date. But if this is either 9-Mar-19 or 10-Mar-19 which are Saturday and Sunday, then you must show 11-Mar-19 as the next workday. (more…)