VB runtime errors...

[savvysam]

Hey there!

So, I have an automated report that I get in Excel that reports various
production milestones, alpha, beta, ship date, etc. Each product has up to 5
versions on the report, denoted by text in one of the columns. I want to
combine all entries of a product that has consistent dates in 4 columns.
This would leave only instances of the product with unique milestones, with
the version columnconcatenating as needed. So, in essence of 7 columns I
want 6 of them to match, and if they do, they should become one with all
versions listed in column C. I've gotten help on this board, but the code
that I have now gives me runtime errors. (I've gotten the 400 error, 1004,
and another that says application or object based error.)

Any ideas for what I can do? TIA!

Here is my code:

Sub Concat()

Dim Iloop As Integer
Dim Numrows As Integer
Dim Counter As Integer

Application.ScreenUpdating = False

Numrows = Range("A65536").End(xlUp).Row
Range("A1:G" & Numrows).Select
Selection.Sort key1:=Range("A1"), Order1:=xlAscending, _
Key2:=Range("B1"), Order2:=xlAscending, Key3:=Range("C1"), _
Order3:=xlAscending, key4:=Range("D1"), Order4:=xlAscending, _
key5:=Range("E1"), Order5:=xlAscending, key6:=Range("F1"),
Order6:=xlAscending, _
key7:=Range("G1"), Order7:=xlAscending, Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
For Iloop = Numrows To 2 Step -1
If Cells(Iloop, "A") = Cells(Iloop - 1, "A") & Cells(Iloop, "B") &
Cells(Iloop - 1, "B") & Cells(Iloop, "D") = Cells(Iloop - 1, "D") _
& Cells(Iloop, "E") = Cells(Iloop - 1, "E") & Cells(Iloop, "F") =
Cells(Iloop - 1, "F") & Cells(Iloop, "G") = Cells(Iloop - 1, "G") Then
Cells(Iloop - 1, "C") = Cells(Iloop - 1, "C") & ", " & Cells(Iloop, "C")
Rows(Iloop).Delete
End If
Next Iloop

Range("A1").Select

Application.ScreenUpdating = True

End Sub

[Dave Peterson]

Just like from the user interface, you only get 3 keys to sort by.

But you can do multiple sorts (manually or in code) to get the order you want.

So use 3 sort commands.

First sort by column G, then sort by D, E, F, and finally sort by A, B, and C.



savvysam wrote:
>
> Hey there!
>
> So, I have an automated report that I get in Excel that reports various
> production milestones, alpha, beta, ship date, etc. Each product has up to 5
> versions on the report, denoted by text in one of the columns. I want to
> combine all entries of a product that has consistent dates in 4 columns.
> This would leave only instances of the product with unique milestones, with
> the version columnconcatenating as needed. So, in essence of 7 columns I
> want 6 of them to match, and if they do, they should become one with all
> versions listed in column C. I've gotten help on this board, but the code
> that I have now gives me runtime errors. (I've gotten the 400 error, 1004,
> and another that says application or object based error.)
>
> Any ideas for what I can do? TIA!
>
> Here is my code:
>
> Sub Concat()
>
> Dim Iloop As Integer
> Dim Numrows As Integer
> Dim Counter As Integer
>
> Application.ScreenUpdating = False
>
> Numrows = Range("A65536").End(xlUp).Row
> Range("A1:G" & Numrows).Select
> Selection.Sort key1:=Range("A1"), Order1:=xlAscending, _
> Key2:=Range("B1"), Order2:=xlAscending, Key3:=Range("C1"), _
> Order3:=xlAscending, key4:=Range("D1"), Order4:=xlAscending, _
> key5:=Range("E1"), Order5:=xlAscending, key6:=Range("F1"),
> Order6:=xlAscending, _
> key7:=Range("G1"), Order7:=xlAscending, Header:=xlYes, OrderCustom:=1, _
> MatchCase:=False, Orientation:=xlTopToBottom
> For Iloop = Numrows To 2 Step -1
> If Cells(Iloop, "A") = Cells(Iloop - 1, "A") & Cells(Iloop, "B") &
> Cells(Iloop - 1, "B") & Cells(Iloop, "D") = Cells(Iloop - 1, "D") _
> & Cells(Iloop, "E") = Cells(Iloop - 1, "E") & Cells(Iloop, "F") =
> Cells(Iloop - 1, "F") & Cells(Iloop, "G") = Cells(Iloop - 1, "G") Then
> Cells(Iloop - 1, "C") = Cells(Iloop - 1, "C") & ", " & Cells(Iloop, "C")
> Rows(Iloop).Delete
> End If
> Next Iloop
>
> Range("A1").Select
>
> Application.ScreenUpdating = True
>
> End Sub

--

Dave Peterson

[savvysam]

(This level of VB is a bit over my head, sorry.) Are you saying that the
sort keys I have in my code should be no more than 3? And if so, can you
give me an example of a sort command for D, E, F? Thanks for your help!!

S

"Dave Peterson" wrote:

> Just like from the user interface, you only get 3 keys to sort by.
>
> But you can do multiple sorts (manually or in code) to get the order you want.
>
> So use 3 sort commands.
>
> First sort by column G, then sort by D, E, F, and finally sort by A, B, and C.
>
>
>
> savvysam wrote:
> >
> > Hey there!
> >
> > So, I have an automated report that I get in Excel that reports various
> > production milestones, alpha, beta, ship date, etc. Each product has up to 5
> > versions on the report, denoted by text in one of the columns. I want to
> > combine all entries of a product that has consistent dates in 4 columns.
> > This would leave only instances of the product with unique milestones, with
> > the version columnconcatenating as needed. So, in essence of 7 columns I
> > want 6 of them to match, and if they do, they should become one with all
> > versions listed in column C. I've gotten help on this board, but the code
> > that I have now gives me runtime errors. (I've gotten the 400 error, 1004,
> > and another that says application or object based error.)
> >
> > Any ideas for what I can do? TIA!
> >
> > Here is my code:
> >
> > Sub Concat()
> >
> > Dim Iloop As Integer
> > Dim Numrows As Integer
> > Dim Counter As Integer
> >
> > Application.ScreenUpdating = False
> >
> > Numrows = Range("A65536").End(xlUp).Row
> > Range("A1:G" & Numrows).Select
> > Selection.Sort key1:=Range("A1"), Order1:=xlAscending, _
> > Key2:=Range("B1"), Order2:=xlAscending, Key3:=Range("C1"), _
> > Order3:=xlAscending, key4:=Range("D1"), Order4:=xlAscending, _
> > key5:=Range("E1"), Order5:=xlAscending, key6:=Range("F1"),
> > Order6:=xlAscending, _
> > key7:=Range("G1"), Order7:=xlAscending, Header:=xlYes, OrderCustom:=1, _
> > MatchCase:=False, Orientation:=xlTopToBottom
> > For Iloop = Numrows To 2 Step -1
> > If Cells(Iloop, "A") = Cells(Iloop - 1, "A") & Cells(Iloop, "B") &
> > Cells(Iloop - 1, "B") & Cells(Iloop, "D") = Cells(Iloop - 1, "D") _
> > & Cells(Iloop, "E") = Cells(Iloop - 1, "E") & Cells(Iloop, "F") =
> > Cells(Iloop - 1, "F") & Cells(Iloop, "G") = Cells(Iloop - 1, "G") Then
> > Cells(Iloop - 1, "C") = Cells(Iloop - 1, "C") & ", " & Cells(Iloop, "C")
> > Rows(Iloop).Delete
> > End If
> > Next Iloop
> >
> > Range("A1").Select
> >
> > Application.ScreenUpdating = True
> >
> > End Sub
>
> --
>
> Dave Peterson
>

[Dave Peterson]

That giant (incorrect) sort statement could be replaced with these 3 sort
statements:

Selection.Sort _
key1:=Range("G1"), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
key1:=Range("D1"), Order1:=xlAscending, _
key2:=Range("E1"), Order2:=xlAscending, _
key3:=Range("F1"), Order3:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
key1:=Range("A1"), Order1:=xlAscending, _
key2:=Range("B1"), Order2:=xlAscending, _
key3:=Range("C1"), Order3:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom



savvysam wrote:
>
> (This level of VB is a bit over my head, sorry.) Are you saying that the
> sort keys I have in my code should be no more than 3? And if so, can you
> give me an example of a sort command for D, E, F? Thanks for your help!!
>
> S
>
> "Dave Peterson" wrote:
>
> > Just like from the user interface, you only get 3 keys to sort by.
> >
> > But you can do multiple sorts (manually or in code) to get the order you want.
> >
> > So use 3 sort commands.
> >
> > First sort by column G, then sort by D, E, F, and finally sort by A, B, and C.
> >
> >
> >
> > savvysam wrote:
> > >
> > > Hey there!
> > >
> > > So, I have an automated report that I get in Excel that reports various
> > > production milestones, alpha, beta, ship date, etc. Each product has up to 5
> > > versions on the report, denoted by text in one of the columns. I want to
> > > combine all entries of a product that has consistent dates in 4 columns.
> > > This would leave only instances of the product with unique milestones, with
> > > the version columnconcatenating as needed. So, in essence of 7 columns I
> > > want 6 of them to match, and if they do, they should become one with all
> > > versions listed in column C. I've gotten help on this board, but the code
> > > that I have now gives me runtime errors. (I've gotten the 400 error, 1004,
> > > and another that says application or object based error.)
> > >
> > > Any ideas for what I can do? TIA!
> > >
> > > Here is my code:
> > >
> > > Sub Concat()
> > >
> > > Dim Iloop As Integer
> > > Dim Numrows As Integer
> > > Dim Counter As Integer
> > >
> > > Application.ScreenUpdating = False
> > >
> > > Numrows = Range("A65536").End(xlUp).Row
> > > Range("A1:G" & Numrows).Select
> > > Selection.Sort key1:=Range("A1"), Order1:=xlAscending, _
> > > Key2:=Range("B1"), Order2:=xlAscending, Key3:=Range("C1"), _
> > > Order3:=xlAscending, key4:=Range("D1"), Order4:=xlAscending, _
> > > key5:=Range("E1"), Order5:=xlAscending, key6:=Range("F1"),
> > > Order6:=xlAscending, _
> > > key7:=Range("G1"), Order7:=xlAscending, Header:=xlYes, OrderCustom:=1, _
> > > MatchCase:=False, Orientation:=xlTopToBottom
> > > For Iloop = Numrows To 2 Step -1
> > > If Cells(Iloop, "A") = Cells(Iloop - 1, "A") & Cells(Iloop, "B") &
> > > Cells(Iloop - 1, "B") & Cells(Iloop, "D") = Cells(Iloop - 1, "D") _
> > > & Cells(Iloop, "E") = Cells(Iloop - 1, "E") & Cells(Iloop, "F") =
> > > Cells(Iloop - 1, "F") & Cells(Iloop, "G") = Cells(Iloop - 1, "G") Then
> > > Cells(Iloop - 1, "C") = Cells(Iloop - 1, "C") & ", " & Cells(Iloop, "C")
> > > Rows(Iloop).Delete
> > > End If
> > > Next Iloop
> > >
> > > Range("A1").Select
> > >
> > > Application.ScreenUpdating = True
> > >
> > > End Sub
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[savvysam]

Thanks, Dave! Hmm, after giving it a try it doesn't seem to be combining the
entries at all now. But, at least no errors, and it's definitely sorting
properly... Here's what I've got now:

Sub Concat()

Dim Iloop As Integer
Dim Numrows As Integer
Dim Counter As Integer

Application.ScreenUpdating = False

Numrows = Range("A65536").End(xlUp).Row
Range("A1:G" & Numrows).Select

Selection.Sort _
key1:=Range("G1"), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
key1:=Range("D1"), Order1:=xlAscending, _
key2:=Range("E1"), Order2:=xlAscending, _
key3:=Range("F1"), Order3:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
key1:=Range("A1"), Order1:=xlAscending, _
key2:=Range("B1"), Order2:=xlAscending, _
key3:=Range("C1"), Order3:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

For Iloop = Numrows To 2 Step -1
If Cells(Iloop, "A") = Cells(Iloop - 1, "A") & Cells(Iloop, "B") =
Cells(Iloop - 1, "B") & _
Cells(Iloop, "G") = Cells(Iloop - 1, "G") Then
Cells(Iloop - 1, "C") = Cells(Iloop - 1, "C") & ", " & Cells(Iloop, "C")
Rows(Iloop).Delete
End If
Next Iloop

Range("A1").Select

Application.ScreenUpdating = True

End Sub

"Dave Peterson" wrote:

> That giant (incorrect) sort statement could be replaced with these 3 sort
> statements:
>
> Selection.Sort _
> key1:=Range("G1"), Order1:=xlAscending, _
> Header:=xlYes, OrderCustom:=1, _
> MatchCase:=False, Orientation:=xlTopToBottom
>
> Selection.Sort _
> key1:=Range("D1"), Order1:=xlAscending, _
> key2:=Range("E1"), Order2:=xlAscending, _
> key3:=Range("F1"), Order3:=xlAscending, _
> Header:=xlYes, OrderCustom:=1, _
> MatchCase:=False, Orientation:=xlTopToBottom
>
> Selection.Sort _
> key1:=Range("A1"), Order1:=xlAscending, _
> key2:=Range("B1"), Order2:=xlAscending, _
> key3:=Range("C1"), Order3:=xlAscending, _
> Header:=xlYes, OrderCustom:=1, _
> MatchCase:=False, Orientation:=xlTopToBottom
>
>
>
> savvysam wrote:
> >
> > (This level of VB is a bit over my head, sorry.) Are you saying that the
> > sort keys I have in my code should be no more than 3? And if so, can you
> > give me an example of a sort command for D, E, F? Thanks for your help!!
> >
> > S
> >
> > "Dave Peterson" wrote:
> >
> > > Just like from the user interface, you only get 3 keys to sort by.
> > >
> > > But you can do multiple sorts (manually or in code) to get the order you want.
> > >
> > > So use 3 sort commands.
> > >
> > > First sort by column G, then sort by D, E, F, and finally sort by A, B, and C.
> > >
> > >
> > >
> > > savvysam wrote:
> > > >
> > > > Hey there!
> > > >
> > > > So, I have an automated report that I get in Excel that reports various
> > > > production milestones, alpha, beta, ship date, etc. Each product has up to 5
> > > > versions on the report, denoted by text in one of the columns. I want to
> > > > combine all entries of a product that has consistent dates in 4 columns.
> > > > This would leave only instances of the product with unique milestones, with
> > > > the version columnconcatenating as needed. So, in essence of 7 columns I
> > > > want 6 of them to match, and if they do, they should become one with all
> > > > versions listed in column C. I've gotten help on this board, but the code
> > > > that I have now gives me runtime errors. (I've gotten the 400 error, 1004,
> > > > and another that says application or object based error.)
> > > >
> > > > Any ideas for what I can do? TIA!
> > > >
> > > > Here is my code:
> > > >
> > > > Sub Concat()
> > > >
> > > > Dim Iloop As Integer
> > > > Dim Numrows As Integer
> > > > Dim Counter As Integer
> > > >
> > > > Application.ScreenUpdating = False
> > > >
> > > > Numrows = Range("A65536").End(xlUp).Row
> > > > Range("A1:G" & Numrows).Select
> > > > Selection.Sort key1:=Range("A1"), Order1:=xlAscending, _
> > > > Key2:=Range("B1"), Order2:=xlAscending, Key3:=Range("C1"), _
> > > > Order3:=xlAscending, key4:=Range("D1"), Order4:=xlAscending, _
> > > > key5:=Range("E1"), Order5:=xlAscending, key6:=Range("F1"),
> > > > Order6:=xlAscending, _
> > > > key7:=Range("G1"), Order7:=xlAscending, Header:=xlYes, OrderCustom:=1, _
> > > > MatchCase:=False, Orientation:=xlTopToBottom
> > > > For Iloop = Numrows To 2 Step -1
> > > > If Cells(Iloop, "A") = Cells(Iloop - 1, "A") & Cells(Iloop, "B") &
> > > > Cells(Iloop - 1, "B") & Cells(Iloop, "D") = Cells(Iloop - 1, "D") _
> > > > & Cells(Iloop, "E") = Cells(Iloop - 1, "E") & Cells(Iloop, "F") =
> > > > Cells(Iloop - 1, "F") & Cells(Iloop, "G") = Cells(Iloop - 1, "G") Then
> > > > Cells(Iloop - 1, "C") = Cells(Iloop - 1, "C") & ", " & Cells(Iloop, "C")
> > > > Rows(Iloop).Delete
> > > > End If
> > > > Next Iloop
> > > >
> > > > Range("A1").Select
> > > >
> > > > Application.ScreenUpdating = True
> > > >
> > > > End Sub
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

[Dave Peterson]

I didn't look at the other portion. But VBA doesn't use & for And. You have to
use And.

Maybe...

Option Explicit
Sub Concat()

Dim Iloop As Long
Dim Numrows As Long
'Dim Counter As Long

Application.ScreenUpdating = False

Numrows = Range("A65536").End(xlUp).Row
Range("A1:G" & Numrows).Select

Selection.Sort _
key1:=Range("G1"), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
key1:=Range("D1"), Order1:=xlAscending, _
key2:=Range("E1"), Order2:=xlAscending, _
key3:=Range("F1"), Order3:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

Selection.Sort _
key1:=Range("A1"), Order1:=xlAscending, _
key2:=Range("B1"), Order2:=xlAscending, _
key3:=Range("C1"), Order3:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

For Iloop = Numrows To 2 Step -1
If Cells(Iloop, "A").Value = Cells(Iloop - 1, "A").Value _
And Cells(Iloop, "B").Value = Cells(Iloop - 1, "B").Value _
And Cells(Iloop, "G").Value = Cells(Iloop - 1, "G").Value Then
Cells(Iloop - 1, "C").Value _
= Cells(Iloop - 1, "C").Value & ", " & Cells(Iloop, "C").Value
Rows(Iloop).Delete
End If
Next Iloop

Range("A1").Select

Application.ScreenUpdating = True

End Sub

I changed Integer to Long--it actually speeds up the processing and I commented
out the counter variable. I didn't see it used in this code. And I like to
specify the property that I'm using (.value) even if it is the default.



savvysam wrote:
>
> Thanks, Dave! Hmm, after giving it a try it doesn't seem to be combining the
> entries at all now. But, at least no errors, and it's definitely sorting
> properly... Here's what I've got now:
>
> Sub Concat()
>
> Dim Iloop As Integer
> Dim Numrows As Integer
> Dim Counter As Integer
>
> Application.ScreenUpdating = False
>
> Numrows = Range("A65536").End(xlUp).Row
> Range("A1:G" & Numrows).Select
>
> Selection.Sort _
> key1:=Range("G1"), Order1:=xlAscending, _
> Header:=xlYes, OrderCustom:=1, _
> MatchCase:=False, Orientation:=xlTopToBottom
>
> Selection.Sort _
> key1:=Range("D1"), Order1:=xlAscending, _
> key2:=Range("E1"), Order2:=xlAscending, _
> key3:=Range("F1"), Order3:=xlAscending, _
> Header:=xlYes, OrderCustom:=1, _
> MatchCase:=False, Orientation:=xlTopToBottom
>
> Selection.Sort _
> key1:=Range("A1"), Order1:=xlAscending, _
> key2:=Range("B1"), Order2:=xlAscending, _
> key3:=Range("C1"), Order3:=xlAscending, _
> Header:=xlYes, OrderCustom:=1, _
> MatchCase:=False, Orientation:=xlTopToBottom
>
> For Iloop = Numrows To 2 Step -1
> If Cells(Iloop, "A") = Cells(Iloop - 1, "A") & Cells(Iloop, "B") =
> Cells(Iloop - 1, "B") & _
> Cells(Iloop, "G") = Cells(Iloop - 1, "G") Then
> Cells(Iloop - 1, "C") = Cells(Iloop - 1, "C") & ", " & Cells(Iloop, "C")
> Rows(Iloop).Delete
> End If
> Next Iloop
>
> Range("A1").Select
>
> Application.ScreenUpdating = True
>
> End Sub
>
> "Dave Peterson" wrote:
>
> > That giant (incorrect) sort statement could be replaced with these 3 sort
> > statements:
> >
> > Selection.Sort _
> > key1:=Range("G1"), Order1:=xlAscending, _
> > Header:=xlYes, OrderCustom:=1, _
> > MatchCase:=False, Orientation:=xlTopToBottom
> >
> > Selection.Sort _
> > key1:=Range("D1"), Order1:=xlAscending, _
> > key2:=Range("E1"), Order2:=xlAscending, _
> > key3:=Range("F1"), Order3:=xlAscending, _
> > Header:=xlYes, OrderCustom:=1, _
> > MatchCase:=False, Orientation:=xlTopToBottom
> >
> > Selection.Sort _
> > key1:=Range("A1"), Order1:=xlAscending, _
> > key2:=Range("B1"), Order2:=xlAscending, _
> > key3:=Range("C1"), Order3:=xlAscending, _
> > Header:=xlYes, OrderCustom:=1, _
> > MatchCase:=False, Orientation:=xlTopToBottom
> >
> >
> >
> > savvysam wrote:
> > >
> > > (This level of VB is a bit over my head, sorry.) Are you saying that the
> > > sort keys I have in my code should be no more than 3? And if so, can you
> > > give me an example of a sort command for D, E, F? Thanks for your help!!
> > >
> > > S
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Just like from the user interface, you only get 3 keys to sort by.
> > > >
> > > > But you can do multiple sorts (manually or in code) to get the order you want.
> > > >
> > > > So use 3 sort commands.
> > > >
> > > > First sort by column G, then sort by D, E, F, and finally sort by A, B, and C.
> > > >
> > > >
> > > >
> > > > savvysam wrote:
> > > > >
> > > > > Hey there!
> > > > >
> > > > > So, I have an automated report that I get in Excel that reports various
> > > > > production milestones, alpha, beta, ship date, etc. Each product has up to 5
> > > > > versions on the report, denoted by text in one of the columns. I want to
> > > > > combine all entries of a product that has consistent dates in 4 columns.
> > > > > This would leave only instances of the product with unique milestones, with
> > > > > the version columnconcatenating as needed. So, in essence of 7 columns I
> > > > > want 6 of them to match, and if they do, they should become one with all
> > > > > versions listed in column C. I've gotten help on this board, but the code
> > > > > that I have now gives me runtime errors. (I've gotten the 400 error, 1004,
> > > > > and another that says application or object based error.)
> > > > >
> > > > > Any ideas for what I can do? TIA!
> > > > >
> > > > > Here is my code:
> > > > >
> > > > > Sub Concat()
> > > > >
> > > > > Dim Iloop As Integer
> > > > > Dim Numrows As Integer
> > > > > Dim Counter As Integer
> > > > >
> > > > > Application.ScreenUpdating = False
> > > > >
> > > > > Numrows = Range("A65536").End(xlUp).Row
> > > > > Range("A1:G" & Numrows).Select
> > > > > Selection.Sort key1:=Range("A1"), Order1:=xlAscending, _
> > > > > Key2:=Range("B1"), Order2:=xlAscending, Key3:=Range("C1"), _
> > > > > Order3:=xlAscending, key4:=Range("D1"), Order4:=xlAscending, _
> > > > > key5:=Range("E1"), Order5:=xlAscending, key6:=Range("F1"),
> > > > > Order6:=xlAscending, _
> > > > > key7:=Range("G1"), Order7:=xlAscending, Header:=xlYes, OrderCustom:=1, _
> > > > > MatchCase:=False, Orientation:=xlTopToBottom
> > > > > For Iloop = Numrows To 2 Step -1
> > > > > If Cells(Iloop, "A") = Cells(Iloop - 1, "A") & Cells(Iloop, "B") &
> > > > > Cells(Iloop - 1, "B") & Cells(Iloop, "D") = Cells(Iloop - 1, "D") _
> > > > > & Cells(Iloop, "E") = Cells(Iloop - 1, "E") & Cells(Iloop, "F") =
> > > > > Cells(Iloop - 1, "F") & Cells(Iloop, "G") = Cells(Iloop - 1, "G") Then
> > > > > Cells(Iloop - 1, "C") = Cells(Iloop - 1, "C") & ", " & Cells(Iloop, "C")
> > > > > Rows(Iloop).Delete
> > > > > End If
> > > > > Next Iloop
> > > > >
> > > > > Range("A1").Select
> > > > >
> > > > > Application.ScreenUpdating = True
> > > > >
> > > > > End Sub
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[savvysam]

YYYAAAAY! That worked. Thanks so much for all your help!! :-)

S

"Dave Peterson" wrote:

> I didn't look at the other portion. But VBA doesn't use & for And. You have to
> use And.
>
> Maybe...
>
> Option Explicit
> Sub Concat()
>
> Dim Iloop As Long
> Dim Numrows As Long
> 'Dim Counter As Long
>
> Application.ScreenUpdating = False
>
> Numrows = Range("A65536").End(xlUp).Row
> Range("A1:G" & Numrows).Select
>
> Selection.Sort _
> key1:=Range("G1"), Order1:=xlAscending, _
> Header:=xlYes, OrderCustom:=1, _
> MatchCase:=False, Orientation:=xlTopToBottom
>
> Selection.Sort _
> key1:=Range("D1"), Order1:=xlAscending, _
> key2:=Range("E1"), Order2:=xlAscending, _
> key3:=Range("F1"), Order3:=xlAscending, _
> Header:=xlYes, OrderCustom:=1, _
> MatchCase:=False, Orientation:=xlTopToBottom
>
> Selection.Sort _
> key1:=Range("A1"), Order1:=xlAscending, _
> key2:=Range("B1"), Order2:=xlAscending, _
> key3:=Range("C1"), Order3:=xlAscending, _
> Header:=xlYes, OrderCustom:=1, _
> MatchCase:=False, Orientation:=xlTopToBottom
>
> For Iloop = Numrows To 2 Step -1
> If Cells(Iloop, "A").Value = Cells(Iloop - 1, "A").Value _
> And Cells(Iloop, "B").Value = Cells(Iloop - 1, "B").Value _
> And Cells(Iloop, "G").Value = Cells(Iloop - 1, "G").Value Then
> Cells(Iloop - 1, "C").Value _
> = Cells(Iloop - 1, "C").Value & ", " & Cells(Iloop, "C").Value
> Rows(Iloop).Delete
> End If
> Next Iloop
>
> Range("A1").Select
>
> Application.ScreenUpdating = True
>
> End Sub
>
> I changed Integer to Long--it actually speeds up the processing and I commented
> out the counter variable. I didn't see it used in this code. And I like to
> specify the property that I'm using (.value) even if it is the default.
>
>
>
> savvysam wrote:
> >
> > Thanks, Dave! Hmm, after giving it a try it doesn't seem to be combining the
> > entries at all now. But, at least no errors, and it's definitely sorting
> > properly... Here's what I've got now:
> >
> > Sub Concat()
> >
> > Dim Iloop As Integer
> > Dim Numrows As Integer
> > Dim Counter As Integer
> >
> > Application.ScreenUpdating = False
> >
> > Numrows = Range("A65536").End(xlUp).Row
> > Range("A1:G" & Numrows).Select
> >
> > Selection.Sort _
> > key1:=Range("G1"), Order1:=xlAscending, _
> > Header:=xlYes, OrderCustom:=1, _
> > MatchCase:=False, Orientation:=xlTopToBottom
> >
> > Selection.Sort _
> > key1:=Range("D1"), Order1:=xlAscending, _
> > key2:=Range("E1"), Order2:=xlAscending, _
> > key3:=Range("F1"), Order3:=xlAscending, _
> > Header:=xlYes, OrderCustom:=1, _
> > MatchCase:=False, Orientation:=xlTopToBottom
> >
> > Selection.Sort _
> > key1:=Range("A1"), Order1:=xlAscending, _
> > key2:=Range("B1"), Order2:=xlAscending, _
> > key3:=Range("C1"), Order3:=xlAscending, _
> > Header:=xlYes, OrderCustom:=1, _
> > MatchCase:=False, Orientation:=xlTopToBottom
> >
> > For Iloop = Numrows To 2 Step -1
> > If Cells(Iloop, "A") = Cells(Iloop - 1, "A") & Cells(Iloop, "B") =
> > Cells(Iloop - 1, "B") & _
> > Cells(Iloop, "G") = Cells(Iloop - 1, "G") Then
> > Cells(Iloop - 1, "C") = Cells(Iloop - 1, "C") & ", " & Cells(Iloop, "C")
> > Rows(Iloop).Delete
> > End If
> > Next Iloop
> >
> > Range("A1").Select
> >
> > Application.ScreenUpdating = True
> >
> > End Sub
> >
> > "Dave Peterson" wrote:
> >
> > > That giant (incorrect) sort statement could be replaced with these 3 sort
> > > statements:
> > >
> > > Selection.Sort _
> > > key1:=Range("G1"), Order1:=xlAscending, _
> > > Header:=xlYes, OrderCustom:=1, _
> > > MatchCase:=False, Orientation:=xlTopToBottom
> > >
> > > Selection.Sort _
> > > key1:=Range("D1"), Order1:=xlAscending, _
> > > key2:=Range("E1"), Order2:=xlAscending, _
> > > key3:=Range("F1"), Order3:=xlAscending, _
> > > Header:=xlYes, OrderCustom:=1, _
> > > MatchCase:=False, Orientation:=xlTopToBottom
> > >
> > > Selection.Sort _
> > > key1:=Range("A1"), Order1:=xlAscending, _
> > > key2:=Range("B1"), Order2:=xlAscending, _
> > > key3:=Range("C1"), Order3:=xlAscending, _
> > > Header:=xlYes, OrderCustom:=1, _
> > > MatchCase:=False, Orientation:=xlTopToBottom
> > >
> > >
> > >
> > > savvysam wrote:
> > > >
> > > > (This level of VB is a bit over my head, sorry.) Are you saying that the
> > > > sort keys I have in my code should be no more than 3? And if so, can you
> > > > give me an example of a sort command for D, E, F? Thanks for your help!!
> > > >
> > > > S
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > Just like from the user interface, you only get 3 keys to sort by.
> > > > >
> > > > > But you can do multiple sorts (manually or in code) to get the order you want.
> > > > >
> > > > > So use 3 sort commands.
> > > > >
> > > > > First sort by column G, then sort by D, E, F, and finally sort by A, B, and C.
> > > > >
> > > > >
> > > > >
> > > > > savvysam wrote:
> > > > > >
> > > > > > Hey there!
> > > > > >
> > > > > > So, I have an automated report that I get in Excel that reports various
> > > > > > production milestones, alpha, beta, ship date, etc. Each product has up to 5
> > > > > > versions on the report, denoted by text in one of the columns. I want to
> > > > > > combine all entries of a product that has consistent dates in 4 columns.
> > > > > > This would leave only instances of the product with unique milestones, with
> > > > > > the version columnconcatenating as needed. So, in essence of 7 columns I
> > > > > > want 6 of them to match, and if they do, they should become one with all
> > > > > > versions listed in column C. I've gotten help on this board, but the code
> > > > > > that I have now gives me runtime errors. (I've gotten the 400 error, 1004,
> > > > > > and another that says application or object based error.)
> > > > > >
> > > > > > Any ideas for what I can do? TIA!
> > > > > >
> > > > > > Here is my code:
> > > > > >
> > > > > > Sub Concat()
> > > > > >
> > > > > > Dim Iloop As Integer
> > > > > > Dim Numrows As Integer
> > > > > > Dim Counter As Integer
> > > > > >
> > > > > > Application.ScreenUpdating = False
> > > > > >
> > > > > > Numrows = Range("A65536").End(xlUp).Row
> > > > > > Range("A1:G" & Numrows).Select
> > > > > > Selection.Sort key1:=Range("A1"), Order1:=xlAscending, _
> > > > > > Key2:=Range("B1"), Order2:=xlAscending, Key3:=Range("C1"), _
> > > > > > Order3:=xlAscending, key4:=Range("D1"), Order4:=xlAscending, _
> > > > > > key5:=Range("E1"), Order5:=xlAscending, key6:=Range("F1"),
> > > > > > Order6:=xlAscending, _
> > > > > > key7:=Range("G1"), Order7:=xlAscending, Header:=xlYes, OrderCustom:=1, _
> > > > > > MatchCase:=False, Orientation:=xlTopToBottom
> > > > > > For Iloop = Numrows To 2 Step -1
> > > > > > If Cells(Iloop, "A") = Cells(Iloop - 1, "A") & Cells(Iloop, "B") &
> > > > > > Cells(Iloop - 1, "B") & Cells(Iloop, "D") = Cells(Iloop - 1, "D") _
> > > > > > & Cells(Iloop, "E") = Cells(Iloop - 1, "E") & Cells(Iloop, "F") =
> > > > > > Cells(Iloop - 1, "F") & Cells(Iloop, "G") = Cells(Iloop - 1, "G") Then
> > > > > > Cells(Iloop - 1, "C") = Cells(Iloop - 1, "C") & ", " & Cells(Iloop, "C")
> > > > > > Rows(Iloop).Delete
> > > > > > End If
> > > > > > Next Iloop
> > > > > >
> > > > > > Range("A1").Select
> > > > > >
> > > > > > Application.ScreenUpdating = True
> > > > > >
> > > > > > End Sub
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

[Dave Peterson]

Woohoo!!!

<<snipped>>