I just want to convey that the content of this site is completely copyright free. You can use the content the way you want without seeking any permission. Whether you are using it commercially or non-commercially, personally or within your organization, it doesn't matter. There is no need to give any acknowledgement either that you…
Category: VBA
Downloads 22 – Sample CSV Files / Data Sets for Testing (till 5 Million Records) – IBM HR Analytics for Attrition
On Kaggle there is a data set published named "IBM HR Analytics Employee Attrition & Performance" to predict attrition of your valuable employees. This is a very popular dataset and has usability index of 8.8. https://www.kaggle.com/pavansubhasht/ibm-hr-analytics-attrition-dataset But this data set has only 1470 rows whereas we need, sometimes, a large data set for testing. So,…
Downloads 21 – Sample CSV Files / Data Sets for Testing (till 5 Million Records) – Bank Transactions
Disclaimer – The datasets are generated through random logic in VBA. These are not real banking transaction data and should not be used for any other purpose other than testing. Other data sets – Human Resources Credit Card Sales HR Analytics Note – I have been approached for the permission to…
VBA – Optimize File Size by Clearing Unused Area
Sometimes, you find that your file size is very huge even though data contained in not much. The reason for this is that there are unused formatting beyond your data range. Either you can clear off your rows and columns beyond data range but if this is frequent, you can use a macro to do…
Article 48 – Split A Workbook into Multiple Workbooks on the basis of a Column
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…
VBA – Delete All Blank Rows
This macro will delete all blank rows from a sheet.
Tips & Tricks 174 – VBA – OR Condition in FIND
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…
Tips & Tricks 168 – Sum Cells for a Particular Color
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 >…
Tips & Tricks 169 – Get the Source of a Pivot Table
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…
VBA – Create a Clickable Index (Summary, Table of Contents) Sheet Macro
Below is a Macro which inserts a sheet named "Index" as first sheet and puts down all the sheets names which are clickable.