Hi everyone

I have just started learning VBA and a friend designed the following macro.
The macro takes information from Cell A2 to F2 and creates a new record in a single column. That is the is transposed from horizontal across columns to a single column, with line break at each record.

This macro works very well.

I would like to learn more about VBA and apply this knowledge using this macro.


I would appreciate if a member could add some comments against each line so I can understand what the code is doing.

For example is the following selecting the first record down to the last record in the worksheet.

Range("A2").Select ' start point

Selection.End(xlDown).Select ' bottom record
lastRow = ActiveCell.Row


Thanks Davidshe
Sub UpdateData()

' assuming columns don't go beyond the Z column

Dim lastColumn As String, currentRow As String, destinationArray As String 'string = text
Dim i As Integer, lastRow, numOfColumns As Integer, destinationStart As Integer ' Interger = number

Application.ScreenUpdating = False ' opening

Range("A2").Select ' start point

Selection.End(xlDown).Select ' bottom record
lastRow = ActiveCell.Row

Selection.End(xlToRight).Select
numOfColumns = ActiveCell.Column + 1
lastColumn = (Chr(numOfColumns + 64)) ' maximum columns could be 1 to max etc

' should add in a line here to clear old tranposed data

destinationStart = lastRow

For i = 2 To lastRow
currentRow = "A" & i & ":" & lastColumn & i
destinationArray = "A" & destinationStart + 5 & ":A" & destinationStart + 3 + numOfColumns
Range(destinationArray).FormulaArray = "=transpose(" & currentRow & ")"
destinationStart = destinationStart + numOfColumns
Next i

Application.ScreenUpdating = True 'closing

End Sub