How to Rebuild Data, Case Study

Sometimes we deal with data that is not standardized that we might give up, you should learn how to rebuild you data, OK, let’s start the case. Source Data Rebuild Data INDEX formula You might think use INDEX() function, right! but how? The INDEX() function syntax: INDEX(array, row_num, [column_num]) After…

Find and Format All Formulas in a Workbook

This simple macro can help find and format all cells that contain formulas. VBA Code Sub FormatFormulas() ‘Step 1: Declare your Variables Dim ws As Worksheet ‘Step 2: Avoid Error if no formulas are found On Error Resume Next ‘Step 3: Start looping through worksheets For Each ws In ActiveWorkbook.Worksheets…

Delete Blank or Empty Columns Use Excel VBA

Blank columns can often cause problems with formulas. If you find that you are manually searching out and deleting blank columns in your data sets, this simple macro can help automate delete blank columns. If you want to delete blank rows, click here. VBA Code Sub DeleteBlankColumns() ‘Step1: Declare your…

Delete Blank or Empty Rows Use Excel VBA

Blank rows can often cause problems with formulas. If you find that you are manually searching out and deleting blank rows in your data sets, this simple macro can help automate delete empty rows. If you want to delete blank columns, click here. VBA Code Sub DeleteBlankRows() ‘Step1: Declare your…

Unhide All Rows and Columns

This simple macro automatically unhides all rows and columns for you. VBA Code Sub UnhideAll() Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub How This Macro Works In this macro, we call on the Columns collection and the Rows collection of the worksheet. Each collection has properties that dictate where…

Insert Blank Rows Between Existing Rows In Excel VBA

Sometimes, you may need to insert blank rows between each of the existing rows into your Worksheet. Although blank rows are generally bothersome, in some situations, the final formatted version of your report requires them to separate data. This simple macro will help you to do this. If you don’t…

How To Quickly Insert Blank Rows Between Existing Rows In Excel

Sometimes, you may need to dynamically insert blank rows between each of the existing rows into your Worksheet. Although blank rows are generally bothersome, in some situations, the final formatted version of your report requires them to separate data. This tip will help you quickly insert blank rows between multiple…

Loop Through a Range of Cells

One must-have VBA skill is the ability to loop (or enumerate) through a range of cells. If you do any serious macro work in Excel, you will soon encounter the need to go through a range of cells one by one and perform some action. This basic macro shows you…