MS Excel and VBA: Transposing Data

This tip is submitted by Anupam Srivastava:

Transposing Data from Column to Row with a feature to start a New Row for each New Record which is identified by an empty Row:

The original dataset was stored in the same column followed by an empty row before each new group entry. The task was to bring each dataset into row format to make data analysis job easier. This is particularly useful for comparing information.
This Macro scans through the column, copies each item and paste it into different columns in the same row and begins a new row for each empty row it finds. The datasets are differentiated by empty row before new dataset begins.
To maintain consistency with the generated table, it is wise to make sure that each new dataset has the same number of data entities (items).

For users with knowledge of DB, if we consider each group to be an attribute with x number of entities, then each new attribute that you add to the original data should consist x entities to make sure that the final table gets populated in a uniform fashion.

For this Macro it is assumed that each dataset consists of 7 entities


Sub DataTranspose()

‘ Declare variable to denote column and row assignment

' Row Variable
Dim i As Integer
' Column Variable
Dim j As Integer
i = 2
j = 1


' Sheet has 218 rows of data

For i = 1 To 218
Range("A" & i).Select
Selection.Copy
Range("a" & j).Select
ActiveSheet.Paste

' Increment Row Counter to read next row
i = i + 1
Range("A" & i).Select
Selection.Copy

' Transposing Row Entry to Column
Range("B" & j).Select
ActiveSheet.Paste
i = i + 1
Range("A" & i).Select
Selection.Copy
Range("C" & j).Select
ActiveSheet.Paste
i = i + 1
Range("A" & i).Select
Selection.Copy
Range("D" & j).Select
ActiveSheet.Paste
i = i + 1
Range("A" & i).Select
Selection.Copy
Range("E" & j).Select
ActiveSheet.Paste
i = i + 1
Range("A" & i).Select
Selection.Copy
Range("F" & j).Select
ActiveSheet.Paste
i = i + 1
Range("A" & i).Select
Selection.Copy
Range("G" & j).Select
ActiveSheet.Paste
i = i + 1
Range("A" & i).Select
Selection.Copy
Range("H" & j).Select
ActiveSheet.Paste
i = i + 1
Range("A" & i).Select
Selection.Copy
Range("I" & j).Select
ActiveSheet.Paste
i = i + 1
j = j + 1
Next

End Sub


----------------------------------------------------------------------------------
Anupam Srivastava is B.Sc. (Computer Engineering) from Queen’s University, Kingston, Canada.

MS Excel: Always show full menu

Turn off the frequently used menu option and always use full menu option during your learning process of MS Excel and remember, learning is a continuous process and you are always learning. So make the full menu option as a regular option for you.
MS Excel only shows you frequently used menu under each tab and then, if you wish to use something other than shown in the menu, you have to go to that expand sign to see full menu:




This is frustrating, especially when you are learning. How are you suppose to know, what is hidden under that magic expand sign. I always prefer to keep full menu on.
To do this, it is a very simple two step process:

Step 1: Go to Tools, find out Customize and click:





Now, you will see something like this:





Step 2: Click on Options tab and select, always show full menus, close and you are done.

Now, every time you click on a menu, you will see full menu. Remember, by doing this on MS Excel, your all other MS Office programs like MS Words etc. will also show full menu items and it is not possible to avoid this. But who cares, it is always good to keep full menu in view irrespective of program you are using.

MS Excel : The Leading Zeros

MS Excel keeps removing that leading zero from your number?
Excel automatically sees the data as a number and leading zeros have no value in numbers, so it gets rid of them.
It is helpful when you're actually dealing with numbers, but irritating when it's a Item number or Employee number.
There is a quick way to get Excel to leave your zeros alone. Justchange the cell format to text. There are couple of ways to make this change—
1. Highlight the cells to change.
Then go to the Format menu, Cells choice. (Ctrl + 1 works too.)
On the General tab choose Text from the Categories list.
Click OK.
The leading zeros are there.
2.If you only have a few cells to change you could just type an apostrophe before entering the data (ie. instead of typing 01122 in the cell type '011122).
When you hit the Enter key the change is made instantly, for that cell only, and again your zero is there.

Outlook SaveAs Type Constants

Constant Value
olTXT 0
olRTF 1
olTemplate 2
olMSG 3
olDoc 4
olHTML 5
olVCard 6
olVCal 7

SQL db to MS Access-How to

Go To SQL Enterprise Manager
>>> Select Server
>>>Open DB
>>>>Tools
>>> Export.. and it will guide you through from there.Don't remember, you probably have to disconnect as well.
Try disconnecting and then, don't forget to connect back. :)