Results 1 to 11 of 11

Concatenate only where there is data

Threaded View

  1. #1
    Registered User
    Join Date
    06-17-2011
    Location
    Boston, MA
    MS-Off Ver
    Excel 2007
    Posts
    52

    Smile Concatenate only where there is data

    Hi All,
    I created a macro (mostly by copying/pasting macros from other posts and tweeking) that:
    1. Inserts a column C
    2. Concatenates A+B into the new column C,
    3. Pastes the values
    4. Shades every other block of new data within column C, and
    5. Deletes the column C that was created.

    The issue that I am having is the fact I don't know how to limit all of the above to only rows that have data in column B. In other words, the macro is doing this for 65,000 lines! How could I limit the above to only rows with data in column B?

    Sub group_and_shade()
    '
    ' group_and_shade Macro
    '
    
    '
    Dim Cll As Range
        Dim Group1Color As Long
        Dim Group2Color As Long
        Dim Group As Integer
        
        Group1Color = -4142 'No Fill
        Group2Color = 15    'Gray
        Group = 1
    
        Range("C1:C65000").Select
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("C1").Select
        ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-2],RC[-1])"
        Selection.Copy
        Range("C1:C65000").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        For Each Cll In Range("C2:C" & Range("C:C").SpecialCells(xlCellTypeLastCell).Row)
            If Cll.Offset(-1, 0).Value <> Cll.Value Then
                Group = Group * -1
            End If
            If Group > 0 Then
                Cll.EntireRow.Interior.ColorIndex = Group1Color
            Else
                Cll.EntireRow.Interior.ColorIndex = Group2Color
            End If
        Next Cll
        Selection.Delete Shift:=xlToLeft
    End Sub
    As always, thank you kindly for any assistance you are able to offer.
    Bryan
    Last edited by tiger10012; 10-05-2011 at 03:57 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