VBA Transpose Dataset (Guidance)

Question

Currently I have a data-set of 4000 rows with data arranged below:

The format it needs to be in is like this:

I have ignored the dates field or the X,Y,Z fields at the moment and just want to focus on the rows. I'm new to VBA still so please bear with my explanations.

My understanding of this is that I should use a variant to store the data as 1-dimensional arrays and then cycle through this via a for-loop.

This is what my code attempts to do (albeit clumsily):

Sub TransposeData()
Dim Last As Variant
Application.ScreenUpdating = False
prevCalcMode = Application.Calculation
Application.Calculation = xlCalculationManual

Last = Cells(Rows.Count, "L").End(xlUp).Row
'Go to the very bottom of row L and get the count
'For i = row Count  - 1 from this and check what the value of L is
'If the value of L is greater than 0 Then ...
For i = Last To 1 Step -1
    If (Cells(i, "L").Value) > 0 Then
     range("D" & i & ":L" & i).Copy
     Sheets("test").Select
     Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True
     Sheets("CVM").Select
     
    End If
Next i

Application.Calculation = prevCalcMode
Application.ScreenUpdating = True


End Sub

However I am stuck at setting my 'range' variable as I don't know how to make it specific to each iteration. i.e. Range(i,L) This will not work obviously but I can't seem to think of another way around this.

Could you please point me in the right direction? I did look at a few other VBA questions regarding this but I couldn't apply the same methodology to my issue. (Transpose a range in VBA)

Thank you!

EDIT: I now have my macro starting to work (yay!), but the loop keeps over-writing the data. Is there a way to check where the data was last pasted and make sure you paste in the next blank part of the column?

One problem is that your `range("D&i:L&i").Copy` will use a range D&i:L&i. What it needs to be is `range("D"& i & ":L" & i).Copy` — Jean-Pierre Oosthuizen, Apr 21 '16 at 08:37
I just realised this as it is taken in as a "string" rather than the proper form. Thanks! I've changed this now! — IronKirby, Apr 21 '16 at 08:42
You need to iterate your paste position, currently you just pasting the selection of the "test" sheet. I have answered your question below — Jean-Pierre Oosthuizen, Apr 21 '16 at 09:13

score 0 · Accepted Answer · answered Apr 21 '16 at 09:18

Seeing as you are new to VBA, as you said.

A few things:

Always use indexed based reference, like you used for range("D" & i & ":L" & i).Copy but then you did not use it for the PasteSpecial

Make sure you use referencing to the specific sheet you are wanting to operate out of, this way VBA doesnt need to assume anything

Try use descriptive variables this helps the next user really understand your code.

Also Use Option Explicit ALWAYS, I did no like it in the beginning but once I was used to typing correct variables for everything, like we should, its not an issue anymore. To have the Option Explicit on every module just go

Tool >> Options >> Require Variable Declaration

See answer below

 Option Explicit
 Sub TransposeData()

      Application.ScreenUpdating = False

      Dim PrevCalcMode As Variant
      PrevCalcMode = Application.Calculation
      Application.Calculation = xlCalculationManual

      Dim DataSheet As Worksheet
      Set DataSheet = ThisWorkbook.Sheets("CVM")

      Dim DestinationSheet As Worksheet
      Set DestinationSheet = ThisWorkbook.Sheets("test")

      Dim DataSheetLastCell As Variant
      With DataSheet
           DataSheetLastCell = .Cells(.Rows.Count, "L").End(xlUp).Row
      End With

      Dim DataSheetRowRef As Long
      Dim DestinationSheetNextFreeRow As Long

      For DataSheetRowRef = 2 To DataSheetLastCell

           If Not DataSheet.Cells(DataSheetRowRef, "L") = Empty Then

                DataSheet.Range("D" & DataSheetRowRef & ":L" & DataSheetRowRef).Copy

                With DestinationSheet

                     DestinationSheetNextFreeRow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1

                     .Cells(DestinationSheetNextFreeRow, "B").PasteSpecial Transpose:=True

                End With

           End If

      Next DataSheetRowRef

      Application.ScreenUpdating = True
      PrevCalcMode = Application.Calculation

 End Sub

@azurekirby `Option Explicit` just forces you to explicitly declare (using `Dim`) etc all your variables other wise the an error will occur when you try and run the code. It helps to then assign the correct type of variable if its a `String` or `Long` or `Range`. Having declared your variables properly it also provide for better code review and usage for the next person. — Jean-Pierre Oosthuizen, Apr 22 '16 at 06:30

VBA Transpose Dataset (Guidance)

1 Answers1