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
Bookmarks