Results 1 to 3 of 3

Changing Multiple Columns into Multiple Rows

Threaded View

Tester36 Changing Multiple Columns... 01-06-2011, 08:17 PM
MarvinP Re: Changing Multiple Columns... 01-06-2011, 10:55 PM
Tester36 Re: Changing Multiple Columns... 01-07-2011, 02:11 PM
  1. #1
    Registered User
    Join Date
    01-06-2011
    Location
    San Jose, CA
    MS-Off Ver
    Excel 2003
    Posts
    2

    Changing Multiple Columns into Multiple Rows

    Hello. I'm trying to create a macro that can change multiple columns into multiple rows. I found the following topic which was close, but I wasn't able to tweak the macro to get it to do what I wanted. http://excel.bigresource.com/Track/excel-OzKIXK1u/

    I've attached the file with an example of what I'm trying to do.

    Basically, Columns A-E would need to be duplicated 12 times. The headers from Columns F-I would need to be the values in Column F for those rows, and the values from Columns F-I should be the values for Column G and the values from columns J-M should be the values for Column H.

    The excel file demonstrates it a little better.

    This is the macro I've been trying to tweak, unsuccessfully:

    Public Function ReArrange()
    Dim LastRow As Long
    Dim LastCol As Long
    Dim i As Long, j As Long
    
        With ActiveSheet
        
            LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
            For i = LastRow To 2 Step -1
            
                LastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
                For j = LastCol - 2 To 2 Step -3
                
                    If Application.CountBlank(.Cells(i, j).Resize(, 3)) < 3 Then
                    
                        .Rows(i + 1).Insert
                        .Cells(i + 1, "A").Value = .Cells(i, "A").Value
                        .Cells(i, j).Resize(, 3).Copy .Cells(i + 1, "B")
                    End If
                Next j
                If Application.CountBlank(.Cells(i, 2).Resize(, 3)) = 3 Then .Rows(i).Delete
            Next i
            .Columns("E:P").Delete
        End With
    End Function
    Thanks,
    Tester36
    Attached Files Attached Files
    Last edited by Tester36; 01-07-2011 at 02:17 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1