Merging Rows

farronblanc · Jun 21st 2004

So Test would be row 1 test2 would be row 2. As opposed to test2 being being row 4.

I used JoinRows macro found at mvps.org/dmcritchie/excel/join.htm But am having trouble modify it to see if there are blanks in column A.

Any help or ideas would be greatly appreciated.

Thanks

lasw10 · Jun 21st 2004

do you want to delete those rows where a is blank once you have merged them?

Jong · Jun 21st 2004

answer

Ok, I'm not sure if i completely understand your question, but the following chunk of code will eliminate any blank cells by moving data to the first available row.
-----------------------

Code

Dim RowPtr as integer
Dim RowFinder as integer
RowPtr = 1


'Get first available cell (first blank cell)
While Worksheets("Worksheetname").Range("A" & RowPtr) <> ""
     RowPtr = RowPtr + 1
wend


'Rowfinder will become the first blank row
RowFinder = RowPtr


While RowFinder <> 9999  'MAX COUNT THAT YOU WANT
     'Find the next row with data
     While Worksheets("Worksheetname").Range("A" & RowFinder) = ""    'A can be any column
          RowFinder = RowFinder + 1
     wend


     'cut the original, and paste it at its new location
     Worksheets("Worksheetname").Range("A" & RowPtr) = Worksheets("Worksheetname").Range("A" & RowFinder)
     Worksheets("Worksheetname").Range("A" & RowFinder) = ""




     RowPtr = RowPtr + 1
 
Wend

Display More

lasw10 · Jun 21st 2004

Assuming not deleting rows where Col A is blank

Code

Sub a()


n = 1 'first row of data
Do Until Cells(n, 2) = ""
    
    'first column in which to post merged data
    c = 3
    If Cells(n, 1) <> "" Then
        n2 = n + 1
        Do Until Cells(n2, 1) <> "" Or Cells(n2, 2) = ""
            Cells(n, c) = Cells(n2, 2)
        c = c + 1
        n2 = n2 + 1
        Loop
    End If
n = n + 1
Loop


End Sub

Display More

farronblanc · Jun 21st 2004

merging rows

Hello.
Thank you for the quick replies.
I want to merge the rows vertically, but not delete them.
Col 1 : Col 2 : Col 3
Row 1: test 34 valid
Row 2: test 34 ic audit
Row 3: more 23 3e

desired result
col 1 : col2: col3
Row 1: test 34 valid
test test test 34 ic audit
Row 2: more 23 3e

I hope the above is clear.
Farron

Merging Rows

Participate now!

Share