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…)

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



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…)

Sat 23 Feb 2019

Tips & Tricks 168 - Sum Cells for a Particular Color

By |Saturday, February 23rd, 2019|Categories: Tips and Tricks, VBA|Tags: , , , , , , , |1 Comment

This can be accomplished with VBA only. There is no native formula within Excel to support this. This is a fairly small piece of code..

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. Copy and Paste the below code in the module
6. Save your file as .xlsm

7. Call your macro as

=SumColorCells(A1:C6,255,255,0)

In place of A1:C6, your can put your range.

Next 3 digits are RGB codes. For Yellow, they are 255, 255 and 0...To know the RGB code of any colour, select your cell and click on down pointing arrow on Fill Colour symbol and go to custom to pick up RGB code.

1

1

---- Macro by E for Excel ----

Function SumColorCells(Rng As Range, R, G, B)
    Dim Cell As Range
    Application.Volatile
    For Each Cell In Rng
        If IsNumeric(Cell) And Cell.Interior.Color = RGB(R, G, B) Then
            SumColorCells = SumColorCells + Cell
        End If
    Next Cell
End Function

 

Fri 06 Jul 2018

Tips & Tricks 169 - Get the Source of a Pivot Table

By |Friday, July 06th, 2018|Categories: Tips and Tricks, VBA|Tags: , , , , , , |0 Comments

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. Copy & Paste the below code in this module

Now, you can call this function like

=GetPivotRange(E5)

Where E5 is any cell in your Pivot Table

' *** Macro by eforexcel.com *****
Function GetPivotRange(Cell As Range) As String
    GetPivotRange = Application.ConvertFormula(Cell.PivotTable.SourceData, xlR1C1, xlA1)
End Function

 

Sat 25 Nov 2017

VBA - Create an Index (Summary, Table of Contents) Sheet Macro

By |Saturday, November 25th, 2017|Categories: VBA|Tags: , , , , , |1 Comment

Below is a Macro which inserts a sheet named "Index" as first sheet and puts down all the sheets names which are clickable.

(more…)

Sat 11 Nov 2017

VBA - Function to Validate IP Addresss

By |Saturday, November 11th, 2017|Categories: VBA|0 Comments

Below is a function which will return True or False if a valid IP address is entered. A valid IP address is of the form nnn.nnn.nnn.nnn where nnn >=0 and <=255

You can put following type of construct in a cell

=IsValidIP(A2)

(more…)

Sat 21 Oct 2017

VBA - Macro to Clean Non-printable characters

By |Saturday, October 21st, 2017|Categories: VBA|Tags: , , , , , |2 Comments

Sometimes, your data contains some characters which make some portion of your data unusable. Below is a macro which cleans your worksheet in the following way -

  1. It will remove non-printable characters with ASCII codes 0 to 31.
  2. It will remove leading and trailing blanks.
  3. Will remove characters with ASCII codes 127, 129, 141, 143, 144, 157, 160.

(more…)

Sat 23 Sep 2017

VBA - Macro to Combine (Append) Sheets

By |Saturday, September 23rd, 2017|Categories: VBA|Tags: , , , , , , |1 Comment

Many times, we need to combine worksheets together. Below is a Macro to do this.

You just need to change the parameter in "Change Parameters in this Section".

  1. In case of many sheets, it will merge all sheets.
  2. If you don't want to merge all sheets but few sheets, just create two sheets named "Start" and "Finish" and move all sheets between these Start and Finish. The macro will merge all sheets between Start and Finish.

(more…)

Sat 26 Aug 2017

Downloads 18 - Sample CSV Files / Data Sets for Testing (till 1.5 Million Records) - Sales

By |Saturday, August 26th, 2017|Categories: Downloads, VBA|Tags: , , , , , , , , , , , , , , , , , , , , , |38 Comments

Disclaimer - The datasets are generated through random logic in VBA. These are not real sales data and should not be used for any other purpose other than testing.

Other data sets - Human Resources   Credit Card

You can download sample csv files ranging from 100 records to 1500000 records. 1.5 Million records will cross 1 million limit of Excel. But 1.5 Million Records are useful for Power Query / Power Pivot. These csv files contain data in various formats like Text and Numbers which should satisfy your need for testing.

This data set can be categorized under "Sales" category.

Below are the fields which appear as part of these csv files as first line.

(more…)