Thursday, December 10, 2015

How to return column letter of a cell in excel?

Excel is cell based data processing application. Mostly, used for finance and reporting purpose in daily life. It uses various Functions/Formulas to generate output from various measures. Most of the functions in excel make use of cells which is intersection of row and column. Thus, in excel formula requires column and row index as a input which in nothing but cell Address. Many times, we need column letter as a input in calculation.

On other hand, Excel does not have any function which will provide column letter as a result. Therefore. someone needs to write complex formulas to get column letter as a output. And writing complex formula in excel is quite time consuming task. which results into slowdown of excel calculation task and make excel heavy. As a result excel take more time to open the file.

Tuesday, December 8, 2015

Excel VBA user defined function to remove special characters from text in excel

Many times we need to remove specific character from text string in excel worksheet. We usually perform this activity as a data cleansing process before generating reports/dashboard in reporting tools like Microsoft Excel, Tibco Spotfire, Tableau, Qlikview and so on. Removing specific character from excel cells manually is a quiet time consuming task. Also, there might be a possibility to forget to miss few characters in data which is not adequate.

We can automate this task by creating a user defined function which will be used to delete specific character from the input text. We can customize a function in such a way that it will remove specific character or special character from input text.

Tuesday, December 1, 2015

How to delete all Pivot tables in active workbook using VBA code?

Pivot table is a very useful and powerful functionality for summarizing or calculating data in Excel, So we may usually insert Pivot tables into a worksheet or multiple worksheets.But, Sometimes we need to delete all pivot tables from active workbook to create brand new  pivot tables which Target new data and design. It will be a very time consuming task to delete all pivot table from workbook which contains large number of pivot table created.

Do you know how to delete all pivot tables in the whole workbook?