Microsoft Office Tutorials and References
In Depth Information
Converting Data to a Tabular Format
Scenario, continued: However, this layout does not allow
you to compile and analyze the data efficiently with Excel.
This macro converts the previous data into a more useful
format that can be analyzed with Excel’s built-in tools.
Figure 31 – Great Data Layout for Data Analysis
View the Appendix to learn how to store this procedure
in a Standard module.
Option Explicit¶
' * * * * *¶
Sub DataToTabular()¶
'Variable declaration¶
Dim RngOrig As Range¶
Dim RngDest As Range¶
Dim FixedColumnCount As Long¶
Dim VarColumnCount As Long¶
Dim i As Long¶
On Error Resume Next¶
'Ask for the data¶
Set RngOrig = Application.InputBox( _¶
Prompt:="Select the data to be converted " & _¶
"(Inlcude the headers)", _¶
Title:="Data", Type:=8)¶
Set RngDest = Application.InputBox( _¶
Prompt:="Where do you want the data " & _¶
"(Select top left cell only)", _¶
Title:="Destination", Type:=8)¶
Search JabSto ::

Custom Search