Macro for Changing Format

**raj1985** · 07-13-2010, 05:42 PM

Hi,

I have an excel file where some data comes in form of vertical columns. I need to allign data appropriately . Also, if there are junk characters like #NA or spaces in the numeric field or if date field is blank, I need to skip that record.

Attached is the sample excel file with two tabs - Input and Output.

I would really appreciate if somebody can help in writing a macro for this.

**rylo** · 07-13-2010, 08:21 PM

Hi

See how this goes.

Sub aaa()
  Dim DataSH As Worksheet, OutSH As Worksheet
  Set OutSH = Sheets("Output")
  Set DataSH = Sheets("Input")
  OutSH.Range("A:C").ClearContents
  DataSH.Activate
  For i = 1 To Cells(2, Columns.Count).End(xlToLeft).Column Step 3
    For j = 4 To Cells(Rows.Count, i).End(xlUp).Row
      If Not IsEmpty(Cells(j, i)) And Not IsEmpty(Cells(j, i + 1)) And IsNumeric(Cells(j, i + 1)) Then
        outrow = OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        OutSH.Cells(outrow, 1).Value = Cells(2, i).Value
        OutSH.Cells(outrow, 2).Value = Cells(j, i).Value
        OutSH.Cells(outrow, 3).Value = Cells(j, i + 1).Value
      End If
    Next j
  Next i
  
  OutSH.Rows("1:1").Delete shift:=xlUp
  
End Sub

rylo