Results 1 to 3 of 3

Thread: Macro help

  1. #1
    Join Date
    Dec 2015
    Posts
    2

    Question Answered: Macro help

    Hi there.
    This is outside of my scope, but I'm trying to do it w/o much luck. Hopefully someone here can help

    I have an excel export for which row 1 has 3 values (columns)

    A B C
    APT-111 12222 111.47

    I need it to display like this

    A B
    APT-111 12222
    APT-111 111.47

    This would be for the entire sheet.

    Any help would be greatly appreciated.

  2. Best Answer
    Posted by MikeTheBike

    "Hi

    Just a little routine that my give you some ideas
    Code:
    Option Explicit
    
    Sub ReArrange()
        Dim iRow As Integer
        Dim i As Integer
        Dim Data() As String
        
        iRow = 1
        i = 1
        Do Until Cells(iRow, 1) = ""
            ReDim Preserve Data(1 To 3, 1 To i)
            Data(1, i) = Cells(iRow, 1)
            Data(2, i) = Cells(iRow, 2)
            Data(3, i) = Cells(iRow, 3)
            iRow = iRow + 1
            i = i + 1
        Loop
        
        If i > 1 Then ' i IS GREATER THAN ONE IF DATA FOUND
    
            'REMOVE THE NEXT LINE IF YOU WANT TO REPLACE THE DATA IN THE EXISTING SHEET
            Sheets.Add After:=Sheets(Sheets.Count)
            
            iRow = 1
            For i = 1 To UBound(Data, 2)
                Cells(iRow, 1) = Data(1, i)
                Cells(iRow, 2) = Data(2, i)
                Cells(iRow, 3) = ""
                iRow = iRow + 1
                Cells(iRow, 1) = Data(1, i)
                Cells(iRow, 2) = Data(3, i)
                Cells(iRow, 3) = ""
                iRow = iRow + 1
            Next i
        End If
    End Sub
    There is info on arrays and dimensioning them in Excel help.

    HTH

    MTB"


  3. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Just a little routine that my give you some ideas
    Code:
    Option Explicit
    
    Sub ReArrange()
        Dim iRow As Integer
        Dim i As Integer
        Dim Data() As String
        
        iRow = 1
        i = 1
        Do Until Cells(iRow, 1) = ""
            ReDim Preserve Data(1 To 3, 1 To i)
            Data(1, i) = Cells(iRow, 1)
            Data(2, i) = Cells(iRow, 2)
            Data(3, i) = Cells(iRow, 3)
            iRow = iRow + 1
            i = i + 1
        Loop
        
        If i > 1 Then ' i IS GREATER THAN ONE IF DATA FOUND
    
            'REMOVE THE NEXT LINE IF YOU WANT TO REPLACE THE DATA IN THE EXISTING SHEET
            Sheets.Add After:=Sheets(Sheets.Count)
            
            iRow = 1
            For i = 1 To UBound(Data, 2)
                Cells(iRow, 1) = Data(1, i)
                Cells(iRow, 2) = Data(2, i)
                Cells(iRow, 3) = ""
                iRow = iRow + 1
                Cells(iRow, 1) = Data(1, i)
                Cells(iRow, 2) = Data(3, i)
                Cells(iRow, 3) = ""
                iRow = iRow + 1
            Next i
        End If
    End Sub
    There is info on arrays and dimensioning them in Excel help.

    HTH

    MTB

  4. #3
    Join Date
    Dec 2015
    Posts
    2
    Thank you so much, that's exactly it
    You rock!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •