Formatting Repeated Values

  • Thread starter OriginalStealth
  • Start date
O

OriginalStealth

ID Title Theater City Date
1234 Matrix AMC Buckhead 1/29/2004
1234 Matrix AMC Buckhead 1/30/2004
1234 Matrix AMC Buckhead 1/31/2004
1222 Matrix2 AMC Jonesboro 1/31/2004

If the ID is the same how can I format (via formula or VBA)the duplicate row to
read:

ID Title Theater City Date

1234 Matrix AMC Buckhead 1/29/2004
"" "" "" "" 1/30/2004
"" "" "" "" 1/31/2004
1222 Matrix2 AMC Jonesboro 1/31/2004


thanks in advance
OS
 
N

Nick Hodge

OS

If you copy this code and highlight the ID column before running it it
should work. (Back up before trying)

Sub insertDitto()
Dim lCounter As Long
Dim rSelection As Range
Set rSelection = Selection
lCounter = rSelection.Rows.Count
For lCounter = lCounter To 1 Step -1
If rSelection.Cells(lCounter).Value = rSelection.Cells(lCounter).Offset(-1,
0).Value Then
rSelection.Cells(lCounter).Value = """"
rSelection.Cells(lCounter).Offset(0, 1).Value = """"
rSelection.Cells(lCounter).Offset(0, 2).Value = """"
rSelection.Cells(lCounter).Offset(0, 3).Value = """"
End If
Next lCounter
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
 
Top