Sat 06 Jan 2018

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

By |Saturday, January 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

 

Sat 23 Dec 2017

Tips & Tricks 168 - Sum Cells for a Particular Color

By |Saturday, December 23rd, 2017|Categories: Tips and Tricks, VBA|Tags: , , , , , , , |0 Comments

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

 

Sat 25 Nov 2017

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

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

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: , , , , , |0 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: , , , , , , , , , , , , , , , , , , , , , |0 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.

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

Sat 19 Aug 2017

Downloads 17 - Sample CSV Files / Data Sets for Testing (till 1.5 Million Records) - Credit Card

By |Saturday, August 19th, 2017|Categories: Downloads, VBA|Tags: , , , , , , , , , , , , , , , , , |1 Comment

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

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 "Credit Card" category.

The data generated follow all known rules for credit cards.

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

(more…)

Sat 12 Aug 2017

Downloads 16 - Sample CSV Files / Data Sets for Testing - Human Resources

By |Saturday, August 12th, 2017|Categories: Downloads, VBA|Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , |0 Comments

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

You can download sample csv files ranging from 100 records to 500000 records. These csv files contain data in various formats like Text, Numbers, Date, Time, Percentages which should satisfy your need for testing.

This data set can be categorized under "Human Resources" category.

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

(more…)

Sat 05 Aug 2017

VBA - Data Masking or Anonymize the Data through a Macro

By |Saturday, August 05th, 2017|Categories: VBA|Tags: , , , , , , , , , , , , , , |1 Comment

Sometimes, you may need to sanitize your data before sharing your file. The below macro would sanitize the data and since this is completely based on random generation, hence, it can not be recreated.

Make sure that you have a copy of the Excel sheet before you sanitize or mask or anonymize the data by running this data. As once done, the data can not be recreated.

The Excel file related to this article can be downloaded from Data Masking Macro

(more…)