Code to merge set of rows

KORKIS2

Board Regular
Joined
Jun 5, 2015
Messages
143
I have code to search down a column for a range of rows now i need code to take that range and merge it all togeher in
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,711
Office Version
  1. 2010
Platform
  1. Windows
I have code to search down a column for a range of rows now i need code to take that range and merge it all togeher in
I think you will need to post an example of your data before the macro does anything and what you want after the macro does its thing (and tell us where you want it). The add-in shown in my signature line could help you in this unless you have method of posting a sample workbook.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,968
Office Version
  1. 365
Platform
  1. Windows
We have noticed that you keep posting questions that are woefully short on details. Remember, we have never seen your worksheets or data. All that we have to go on is what you provide to us here. So don't skimp on details! Otherwise, you may not get responses that work for your exact situation.

Please look at this post here: http://www.mrexcel.com/forum/board-announcements/685723-help-us-help-you.html
 

KORKIS2

Board Regular
Joined
Jun 5, 2015
Messages
143
Any Error messages you receive:

  • When do you receive them?
  • What is the exact code/formula you've used that resulted in that error?
  • Code:
    Dim lLastRow As Long                                                                        '/// A long Variable stores a value between -2,147,483,648 -2,147,483,647\\\\\Dim aSrc As Variant                                                                         '/// A Variant is a special data type that can contain any kind of data except fixed-length String data. \\\\\
    Dim aResults() As String                                                                    '///item, start row, stop row\\\\
    Dim mm As Long, k As Long                                                                   '/// A long Variable stores a value between -2,147,483,648 -2,147,483,647\\\\\
    Dim sCurrVal As String
    Dim Rng As Range
    
    
    
    
    lLastRow = Worksheets("Dipole Scratchpad 1").Cells(Sheet1.Rows.Count, "A").End(xlUp).Row    '///It is used to find the how many rows contain data in a worksheet that contains data in the column "A". and save it in lLastRow\\\\
    
    
    
    
    aSrc = Worksheets("Dipole Scratchpad 1").Range("A7:A" & lLastRow)                           '///this is your raw data saved in aSrc\\\\
    
    
    sCurrVal = aSrc(1, 1)                                                                       '///set current item,store array in sCurrVal\\\\
    ReDim aResults(1 To 3, 1 To 1)                                                              '///create result array\\\
    aResults(1, 1) = sCurrVal
    aResults(2, 1) = 7 'assumes your data starts in row 1
    
    
    k = 1 'result counter
     
    For i = 1 To UBound(aSrc, 1)
        If aSrc(i, 1) <> sCurrVal Then
            'write stop row value
            aResults(3, k) = i + 5
            'set new search item
            sCurrVal = aSrc(i, 1)
            'load result array
            k = k + 1
            ReDim Preserve aResults(1 To 3, 1 To k)
            aResults(1, k) = sCurrVal
            aResults(2, k) = i + 6
        End If
    Next i
    aResults(3, k) = i + 6 'this will capture the last entry
    
    
    'now write result array to worksheet in 3 columns
    Worksheets("Dipole Scratchpad 1").Range("K1").Resize(k, 3) = Application.Transpose(aResults)
    
    
    Worksheets("Dipole Scratchpad 1").Activate
    Dim x As Long
        Dim y As Long
        
        For x = 1 To Range("K" & Rows.Count).End(xlUp).Row
            If Range("K" & x) = "" Then
                y = Range("K" & x).Row
                Exit For
            End If
        Next x
  • What is the exact wording of the error?
  • There is no error I am pulling the variables that are saved in the array above by the cell value and sorting them alphabetically
  • Code:
    AA = Worksheets("Dipole Scratchpad 1").Cells(1, "L").Value
    
    AB = Worksheets("Dipole Scratchpad 1").Cells(1, "M").Value
    
    
    Range(AA & ":" & AB).Sort Key1:=Range("B7"), Order1:=xlAscending, Header:=xlGuess



Now that the items are organized alphabetically I need to merge the cells by column and by the ranges found above from the alphabetical search
 

KORKIS2

Board Regular
Joined
Jun 5, 2015
Messages
143

ADVERTISEMENT

Is this better could you take another look
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,968
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Partly (you have posted your code).
Now if you could post some sample data, and what you would like your final output to look like, we can recreate your scenario and see exactly where it is you are trying to get to.

By the way, did you create the code yourself, or did you get it from somewhere?
 

KORKIS2

Board Regular
Joined
Jun 5, 2015
Messages
143
851.012578
860.0125913
51.0125Body 105.00105.0150512 868.98751416
51.0125Head 108.00108.0150512
60.0125Head 82.0082.0150512
60.0125Head 83.00102.0150512
60.0125Head 103.00103.0150512
60.0125Body 104.00104.0150512
60.0125Body 109.00110.0150512
68.9875Head 106.00107.0150512
68.9875Body 111.00111.0150512


<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>
</tbody>
 

KORKIS2

Board Regular
Joined
Jun 5, 2015
Messages
143
Ok in the Above post on the left side is the data
On the right side is an array that was pulled from the first post of code that finds the rows ranges of data from column A.
If you you look 51.0125 goes from rows 7- 8.....60.0125 goes from 9-13.
Now from thoughs ranges I need to Alphabetically organize column B in thoughs ranges.
So like this

851.012578
860.0125913
51.0125Body 105.00105.0150512 868.98751416
51.0125Head 108.00108.0150512
60.0125Body 82.0082.0150512
60.0125Body 83.00102.0150512
60.0125Head 103.00103.0150512
60.0125Head 104.00104.0150512
60.0125Head 109.00110.0150512
68.9875Body 106.00107.0150512
68.9875Head 111.00111.0150512

<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>
</tbody>
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Perhaps this:-
Code:
[COLOR="Navy"]Sub[/COLOR] MG16Jun36
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not Dn.Value = "" [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            .Add Dn.Value, Dn
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    .Item(K).Offset(, 1).Sort .Item(K).Offset(, 1)(1), xlAscending
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

Watch MrExcel Video

Forum statistics

Threads
1,123,139
Messages
5,599,956
Members
414,351
Latest member
james27

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top