Results 1 to 6 of 6

Convert Multiple Columns to Rows including blank cells

Threaded View

  1. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Convert Multiple Columns to Rows including blank cells

    Hello BJPhil,

    The macro below has been added to the attached workbook. If have any problems with it, let me know.
    A button (Run) has also been been added to the worksheet to run the macro.
    Sub Macro1()
    
      ' Thread: http://www.excelforum.com/excel-programming-vba-macros/1003920-convert-multiple-columns-to-rows-including-blank-cells.html
      
        Dim c       As Long
        Dim Data    As Variant
        Dim n       As Long
        Dim r       As Long
        Dim Rng     As Range
        Dim Who     As String
        Dim Wks     As Worksheet
        
            Set Wks = Worksheets("Sheet1")
            
            Set Rng = Wks.Range("A1").CurrentRegion
            
            n = (Wks.Cells(1, Columns.Count).End(xlToLeft).Column - 1) / 4
    
                r = Rng.Rows.Count - 1
                ReDim Data(1 To (r * n), 1 To 5)
                n = 1
                
                    For r = 2 To Rng.Rows.Count
                        Who = Rng.Cells(r, 1)
                        
                        For c = 2 To Rng.Columns.Count Step 4
                            Data(n, 2) = Rng.Cells(r, c + 0)
                            Data(n, 3) = Rng.Cells(r, c + 1)
                            Data(n, 4) = Rng.Cells(r, c + 2)
                            Data(n, 5) = Rng.Cells(r, c + 3)
                            
                            x = Application.Match("*", Array(Data(n, 2), Data(n, 3), Data(n, 4), Data(n, 5)), 0)
                            If Not IsError(x) Then
                                Data(n, 1) = Who
                                n = n + 1
                            End If
                        Next c
                    Next r
                    
                Wks.Range("A11:D11").Resize(UBound(Data), 5).Value = Data
                
    End Sub
    Attached Files Attached Files
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Transpose Duplicate rows into columns including blank cells
    By BJPhil in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-07-2014, 12:08 PM
  2. COUNTIFS, multiple criteria and not including blank cells
    By qhoney in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-07-2014, 05:14 PM
  3. Convert many non-blank columns to rows
    By smytgemm in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-25-2013, 09:22 AM
  4. Replies: 1
    Last Post: 11-29-2012, 06:24 PM
  5. Count blank cells within a range not including fully blank rows
    By twofootgiant in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 07-16-2008, 09:43 AM

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