Hi Ted
There should only be:-
1) 1 full stop before Columns("E:E")
2) 1 full stop before Range("IV:IV")
3) 1 full stop before Columns("E:F")
The text messed up slightly in transit - sorry.
--
XL2002
Regards
William
(e-mail address removed)
| Hi William,
|
| Thanks for your help. I try to run this code as a macro
| and I get a syntax error, apparently on the following
| statement:
|
| ..Columns("E:E").AdvancedFilter Action:=xlFilterCopy, _
| CriteriaRange:=.Columns("E:E"), CopyToRange:=.Range("G1"),
| _
| Unique:=True
|
| As I said, I'm not a VB programmer. I suspect there's a
| typo or something easy to fix to avoid this error. Do you
| have a suggestion?
|
| Thanks.
|
|
|
|
| >-----Original Message-----
| >Ted
| >
| >You may want to try this code which assumes your data is
| in Columns E and
| >F.....
| >
| >Sub test()
| >Dim r As Range, c As Range, rr As Range, cc As Range
| >With ActiveSheet
| >..Columns("E:E").AdvancedFilter Action:=xlFilterCopy, _
| >CriteriaRange:=.Columns("E:E"), CopyToRange:=.Range
| ("G1"), _
| >Unique:=True
| >Set r = .Range(.Range("G2"), .Range("G" & Rows.Count).End
| (xlUp))
| >Set rr = .Range(.Range("E2"), .Range("E" & Rows.Count).End
| (xlUp))
| >For Each c In r
| >For Each cc In rr
| >If cc = c Then _
| >Application.Intersect(c.EntireRow, _
| >..Range("IV:IV")).End(xlToLeft).Offset(0, 1) = cc.Offset
| (0, 1)
| >Next cc
| >Next c
| >..Columns("E:F").Delete Shift:=xlToLeft
| >End With
| >End Sub
| >
| >
| >--
| >XL2002
| >Regards
| >
| >William
| >
| >
[email protected]
| >
| in message
| >| >| I have data in two columns that looks like this:
| >| xxx 2
| >| xxx 5
| >| xxx 7
| >| yyy 9
| >| yyy 10
| >| aaa 8
| >| aaa 6
| >| bbb 4
| >| ccc 3
| >| ccc 15
| >| ccc 1
| >| ccc 20
| >| ccc 11
| >| I need to end up with this:
| >| xxx 2 5 7
| >| yyy 9 10
| >| aaa 8 6
| >| bbb 4
| >| ccc 3 15 1 20 11
| >|
| >| I know how to do it manually or using copy/paste
| >| special/transpose but that's really tedious. I want a
| >| formula I can enter and copy (using if and offset???)
| or a
| >| macro I can run to do this for a list that contains
| >| hundreds of rows.
| >| I'm an intermediate / advanced excel user, but not a VB
| >| programmer. Office XP, W2KPro.
| >|
| >| Thanks.
| >|
| >|
| >
| >
| >
| >.
| >
