Adding Break or to Stop macro at certain rows?

S

sezuh

Well-known Member
Joined
Nov 19, 2010
Messages
708
Hi,
Thanks ever so much for the help i've got before,but i need your help again if you dont mind.
I have this Vb code it works fine but is there any way to add break point or stopping it at certain rows? I'll appreciate for any help or suggestion.
For an example i want Vb code to stop at 3481st rows instead of 4845,
is it Possible?
<TABLE style="WIDTH: 353pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=470><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><COL style="WIDTH: 234pt; mso-width-source: userset; mso-width-alt: 11410" width=312><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 62pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21 width=82>c</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 57pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2797542 class=xl63 width=76> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 234pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=312> </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Row Num.</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=23> 1 2 6 13</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> 1 2 6 13, 1 3 12 14, 1 4 5 8, 1 7 15 16</TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=23> 1 3 12 14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>"</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>" "</TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=23> 1 4 5 8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>"</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> 1 3 12 14, 1 4 5 8, 1 7 15 16, 1 9 10 11</TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=23> 1 7 15 16</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>"</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>" "</TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=23> 1 9 10 11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>"</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> 1 4 5 8, 1 7 15 16, 1 9 10 11, 2 3 7 9</TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=23> 2 3 7 9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>"</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>" "</TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=23> 2 4 10 12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>"</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> 1 7 15 16, 1 9 10 11, 2 3 7 9, 2 4 10 12</TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=23> 2 5 11 16</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>"</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>" "</TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=23> 2 8 14 15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>3480</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> 1 9 10 11, 6 11 12 15, 8 9 12 16, 10 13 14 16</TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=23> 3 4 6 16</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>CODE To STOP At THIS ROW </TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=23> 3 5 10 15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>3481</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> 2 3 7 9, 2 4 10 12, 2 5 11 16, 2 8 14 15</TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=23> 3 8 11 13</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=23> 4 7 11 14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=23> 4 9 13 15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=23> 5 6 9 14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=23> 5 7 12 13</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=23> 6 7 8 10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=23> 6 11 12 15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=23> 8 9 12 16</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=23>10 13 14 16</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR></TBODY></TABLE>
And here is the code
Code: 
[FONT=Courier New]Option Explicit<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]Dim vAllItems As Variant<o:p></o:p>[/FONT]
[FONT=Courier New]Dim Buffer() As String<o:p></o:p>[/FONT]
[FONT=Courier New]Dim BufferPtr As Long<o:p></o:p>[/FONT]
[FONT=Courier New]Dim Results As Worksheet<o:p></o:p>[/FONT]
[FONT=Courier New]'<o:p></o:p>[/FONT]
[FONT=Courier New]' Myrna Larson, July 25, 2000, Microsoft.Public.Excel.Misc<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]Sub ListPermutationsOrCombinations()<o:p></o:p>[/FONT]
[FONT=Courier New]Dim Rng As Range<o:p></o:p>[/FONT]
[FONT=Courier New]Dim PopSize As Integer<o:p></o:p>[/FONT]
[FONT=Courier New]Dim SetSize As Integer<o:p></o:p>[/FONT]
[FONT=Courier New]Dim Which As String<o:p></o:p>[/FONT]
[FONT=Courier New]Dim n As Double<o:p></o:p>[/FONT]
[FONT=Courier New]Const BufferSize As Long = 4096<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]Worksheets("Sheet1").Range("A1").Select<o:p></o:p>[/FONT]
[FONT=Courier New]Set Rng = Selection.Columns(1).Cells<o:p></o:p>[/FONT]
[FONT=Courier New]If Rng.Cells.Count = 1 Then<o:p></o:p>[/FONT]
[FONT=Courier New]Set Rng = Range(Rng, Rng.End(xlDown))<o:p></o:p>[/FONT]
[FONT=Courier New]End If<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]PopSize = Rng.Cells.Count - 2<o:p></o:p>[/FONT]
[FONT=Courier New]If PopSize < 2 Then GoTo DataError<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]SetSize = Rng.Cells(2).Value<o:p></o:p>[/FONT]
[FONT=Courier New]If SetSize > PopSize Then GoTo DataError<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]Which = UCase$(Rng.Cells(1).Value)<o:p></o:p>[/FONT]
[FONT=Courier New]Select Case Which<o:p></o:p>[/FONT]
[FONT=Courier New]Case "C"<o:p></o:p>[/FONT]
[FONT=Courier New]n = Application.WorksheetFunction.Combin(PopSize, SetSize)<o:p></o:p>[/FONT]
[FONT=Courier New]Case "P"<o:p></o:p>[/FONT]
[FONT=Courier New]n = Application.WorksheetFunction.Permut(PopSize, SetSize)<o:p></o:p>[/FONT]
[FONT=Courier New]Case Else<o:p></o:p>[/FONT]
[FONT=Courier New]GoTo DataError<o:p></o:p>[/FONT]
[FONT=Courier New]End Select<o:p></o:p>[/FONT]
[FONT=Courier New]'If n > Cells.CountLarge Then GoTo DataError<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]Application.ScreenUpdating = False<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]Set Results = Worksheets.Add<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]vAllItems = Rng.Offset(2, 0).Resize(PopSize).Value<o:p></o:p>[/FONT]
[FONT=Courier New]ReDim Buffer(1 To BufferSize) As String<o:p></o:p>[/FONT]
[FONT=Courier New]BufferPtr = 0<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]If Which = "C" Then<o:p></o:p>[/FONT]
[FONT=Courier New]AddCombination PopSize, SetSize<o:p></o:p>[/FONT]
[FONT=Courier New]Else<o:p></o:p>[/FONT]
[FONT=Courier New]AddPermutation PopSize, SetSize<o:p></o:p>[/FONT]
[FONT=Courier New]End If<o:p></o:p>[/FONT]
[FONT=Courier New]vAllItems = 0<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]Application.ScreenUpdating = True<o:p></o:p>[/FONT]
[FONT=Courier New]Exit Sub<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]DataError:<o:p></o:p>[/FONT]
[FONT=Courier New]If n = 0 Then<o:p></o:p>[/FONT]
[FONT=Courier New]Which = "Enter your data in a vertical range of at least 4 cells." _<o:p></o:p>[/FONT]
[FONT=Courier New]& String$(2, 10) _<o:p></o:p>[/FONT]
[FONT=Courier New]& "Top cell must contain the letter C or P, 2nd cell is the Number" _<o:p></o:p>[/FONT]
[FONT=Courier New]& "of items in a subset, the cells below are the values from Which" _<o:p></o:p>[/FONT]
[FONT=Courier New]& "the subset is to be chosen."<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]Else<o:p></o:p>[/FONT]
[FONT=Courier New]Which = "This requires " & Format$(n, "#,##0") & _<o:p></o:p>[/FONT]
[FONT=Courier New]" cells, more than are available on the worksheet!"<o:p></o:p>[/FONT]
[FONT=Courier New]End If<o:p></o:p>[/FONT]
[FONT=Courier New]MsgBox Which, vbOKOnly, "DATA ERROR"<o:p></o:p>[/FONT]
[FONT=Courier New]Exit Sub<o:p></o:p>[/FONT]
[FONT=Courier New]End Sub<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]Private Sub AddPermutation(Optional PopSize As Integer = 0, _<o:p></o:p>[/FONT]
[FONT=Courier New]Optional SetSize As Integer = 0, _<o:p></o:p>[/FONT]
[FONT=Courier New]Optional NextMember As Integer = 0)<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]Static iPopSize As Integer<o:p></o:p>[/FONT]
[FONT=Courier New]Static iSetSize As Integer<o:p></o:p>[/FONT]
[FONT=Courier New]Static SetMembers() As Integer<o:p></o:p>[/FONT]
[FONT=Courier New]Static Used() As Integer<o:p></o:p>[/FONT]
[FONT=Courier New]Dim i As Integer<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]If PopSize <> 0 Then<o:p></o:p>[/FONT]
[FONT=Courier New]iPopSize = PopSize<o:p></o:p>[/FONT]
[FONT=Courier New]iSetSize = SetSize<o:p></o:p>[/FONT]
[FONT=Courier New]ReDim SetMembers(1 To iSetSize) As Integer<o:p></o:p>[/FONT]
[FONT=Courier New]ReDim Used(1 To iPopSize) As Integer<o:p></o:p>[/FONT]
[FONT=Courier New]NextMember = 1<o:p></o:p>[/FONT]
[FONT=Courier New]End If<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]For i = 1 To iPopSize<o:p></o:p>[/FONT]
[FONT=Courier New]If Used(i) = 0 Then<o:p></o:p>[/FONT]
[FONT=Courier New]SetMembers(NextMember) = i<o:p></o:p>[/FONT]
[FONT=Courier New]If NextMember <> iSetSize Then<o:p></o:p>[/FONT]
[FONT=Courier New]Used(i) = True<o:p></o:p>[/FONT]
[FONT=Courier New]AddPermutation , , NextMember + 1<o:p></o:p>[/FONT]
[FONT=Courier New]Used(i) = False<o:p></o:p>[/FONT]
[FONT=Courier New]Else<o:p></o:p>[/FONT]
[FONT=Courier New]SavePermutation SetMembers()<o:p></o:p>[/FONT]
[FONT=Courier New]End If<o:p></o:p>[/FONT]
[FONT=Courier New]End If<o:p></o:p>[/FONT]
[FONT=Courier New]Next i<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]If NextMember = 1 Then<o:p></o:p>[/FONT]
[FONT=Courier New]SavePermutation SetMembers(), True<o:p></o:p>[/FONT]
[FONT=Courier New]Erase SetMembers<o:p></o:p>[/FONT]
[FONT=Courier New]Erase Used<o:p></o:p>[/FONT]
[FONT=Courier New]End If<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]End Sub 'AddPermutation<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]Private Sub AddCombination(Optional PopSize As Integer = 0, _<o:p></o:p>[/FONT]
[FONT=Courier New]Optional SetSize As Integer = 0, _<o:p></o:p>[/FONT]
[FONT=Courier New]Optional NextMember As Integer = 0, _<o:p></o:p>[/FONT]
[FONT=Courier New]Optional NextItem As Integer = 0)<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]Static iPopSize As Integer<o:p></o:p>[/FONT]
[FONT=Courier New]Static iSetSize As Integer<o:p></o:p>[/FONT]
[FONT=Courier New]Static SetMembers() As Integer<o:p></o:p>[/FONT]
[FONT=Courier New]Dim i As Integer<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]If PopSize <> 0 Then<o:p></o:p>[/FONT]
[FONT=Courier New]iPopSize = PopSize<o:p></o:p>[/FONT]
[FONT=Courier New]iSetSize = SetSize<o:p></o:p>[/FONT]
[FONT=Courier New]ReDim SetMembers(1 To iSetSize) As Integer<o:p></o:p>[/FONT]
[FONT=Courier New]NextMember = 1<o:p></o:p>[/FONT]
[FONT=Courier New]NextItem = 1<o:p></o:p>[/FONT]
[FONT=Courier New]End If<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]For i = NextItem To iPopSize<o:p></o:p>[/FONT]
[FONT=Courier New]SetMembers(NextMember) = i<o:p></o:p>[/FONT]
[FONT=Courier New]If NextMember <> iSetSize Then<o:p></o:p>[/FONT]
[FONT=Courier New]AddCombination , , NextMember + 1, i + 1<o:p></o:p>[/FONT]
[FONT=Courier New]Else<o:p></o:p>[/FONT]
[FONT=Courier New]SavePermutation SetMembers()<o:p></o:p>[/FONT]
[FONT=Courier New]End If<o:p></o:p>[/FONT]
[FONT=Courier New]Next i<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]If NextMember = 1 Then<o:p></o:p>[/FONT]
[FONT=Courier New]SavePermutation SetMembers(), True<o:p></o:p>[/FONT]
[FONT=Courier New]Erase SetMembers<o:p></o:p>[/FONT]
[FONT=Courier New]End If<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]End Sub 'AddCombination<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]Private Sub SavePermutation(ItemsChosen() As Integer, _<o:p></o:p>[/FONT]
[FONT=Courier New]Optional FlushBuffer As Boolean = False)<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]Dim i As Integer, sValue As String<o:p></o:p>[/FONT]
[FONT=Courier New]Static RowNum As Long, ColNum As Long<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]If RowNum = 0 Then RowNum = 1<o:p></o:p>[/FONT]
[FONT=Courier New]If ColNum = 0 Then ColNum = 1<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]If FlushBuffer = True Or BufferPtr = UBound(Buffer()) Then<o:p></o:p>[/FONT]
[FONT=Courier New]If BufferPtr > 0 Then<o:p></o:p>[/FONT]
[FONT=Courier New]If (RowNum + BufferPtr - 1) > Rows.Count Then<o:p></o:p>[/FONT]
[FONT=Courier New]RowNum = 1<o:p></o:p>[/FONT]
[FONT=Courier New]ColNum = ColNum + 1<o:p></o:p>[/FONT]
[FONT=Courier New]If ColNum > 256 Then Exit Sub<o:p></o:p>[/FONT]
[FONT=Courier New]End If<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]Results.Cells(RowNum, ColNum).Resize(BufferPtr, 1).Value _<o:p></o:p>[/FONT]
[FONT=Courier New]= Application.WorksheetFunction.Transpose(Buffer())<o:p></o:p>[/FONT]
[FONT=Courier New]RowNum = RowNum + BufferPtr<o:p></o:p>[/FONT]
[FONT=Courier New]End If<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]BufferPtr = 0<o:p></o:p>[/FONT]
[FONT=Courier New]If FlushBuffer = True Then<o:p></o:p>[/FONT]
[FONT=Courier New]Erase Buffer<o:p></o:p>[/FONT]
[FONT=Courier New]RowNum = 0<o:p></o:p>[/FONT]
[FONT=Courier New]ColNum = 0<o:p></o:p>[/FONT]
[FONT=Courier New]Exit Sub<o:p></o:p>[/FONT]
[FONT=Courier New]Else<o:p></o:p>[/FONT]
[FONT=Courier New]ReDim Buffer(1 To UBound(Buffer))<o:p></o:p>[/FONT]
[FONT=Courier New]End If<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]End If<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]'construct the next set<o:p></o:p>[/FONT]
[FONT=Courier New]For i = 1 To UBound(ItemsChosen)<o:p></o:p>[/FONT]
[FONT=Courier New]sValue = sValue & ", " & vAllItems(ItemsChosen(i), 1)<o:p></o:p>[/FONT]
[FONT=Courier New]Next i<o:p></o:p>[/FONT]
[FONT=Courier New]<o:p> </o:p>[/FONT]
[FONT=Courier New]'and save it in the buffer<o:p></o:p>[/FONT]
[FONT=Courier New]BufferPtr = BufferPtr + 1<o:p></o:p>[/FONT]
[FONT=Courier New]Buffer(BufferPtr) = Mid$(sValue, 3)<o:p></o:p>[/FONT]
[FONT=Courier New]End Sub <o:p></o:p>[/FONT]
Thanks again.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Hi,
I would like the Vb code to stop when it finish with rows starting
with number "1" and not to execute the remaining rows starting with 2 ....8 etc.
<TABLE style="WIDTH: 119pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=158><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 62pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2797542 class=xl65 height=20 width=82> 1 2 6 13</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 57pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=76>Yes</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20> 1 3 12 14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Yes</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20> 1 4 5 8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Yes</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20> 1 7 15 16</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Yes</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20> 1 9 10 11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Yes</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20> 2 3 7 9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>No</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20> 2 4 10 12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>No</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20> 2 5 11 16</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>No</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20> 2 8 14 15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>No</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20> 3 4 6 16</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>No</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20> 3 5 10 15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>No</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20> 3 8 11 13</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>No</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20> 4 7 11 14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>No</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20> 4 9 13 15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>No</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20> 5 6 9 14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>No</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20> 5 7 12 13</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>No</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20> 6 7 8 10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>No</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20> 6 11 12 15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>No</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20> 8 9 12 16</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>No</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>10 13 14 16</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>No</TD></TR></TBODY></TABLE>
Thanks
 
Upvote 0
sezuh said:
...I would like the Vb code to stop when it finish with rows starting
with number "1" and not to execute the remaining rows starting with 2 ....8 etc
...
Click to expand...
Hi,
See how the range can be conditionally resized by ShrinkRange subroutine:
Rich (BB code): 

Sub Test()
  
  Dim Rng As Range
  
  ' Set range by the aid of .End(xlUp)
  Set Rng = Range("A1", Cells(Rows.Count, 1).End(xlUp))
  Debug.Print "Before:", Rng.Address(0, 0)  ' Before: A1:A20
  
  ' Conditionally resize Rng
  ShrinkRange Rng, "1 *"
  Debug.Print "After:", Rng.Address(0, 0)   ' After: A1:A5
  
End Sub

' Resizing Rng from the 1st cell up to the lower cell which satisfies Cond condition
Sub ShrinkRange(Rng As Range, Cond)
  Dim x As Range
  Set x = Rng.Find(Cond, Rng(1), xlValues, xlWhole, xlByRows, xlPrevious)
  If Not x Is Nothing Then Set Rng = Range(Rng(1), x)
End Sub
Regards
 
Last edited:
Upvote 0
Seems you are looking for conditional breaking of combinations/permutations generating.
If so then use Myrna Larson's code below tweaked a bit.
Set MoreThanStop constant as stop condition. For your example MoreThanStop=1.
Rich (BB code): 

' 1. Populate active sheet as follows:
'    A1 = "C" (or A1 = "P")
'    A2 = 5
'    A3:A17 = "1"..."15" (one letter per cell)
' 2. Select A1
' 3. Run ListPermutations macro

Option Explicit

'--> ZVI: added
Dim StopIt As Boolean   ' Stop flag
Const MoreThanStop = 1  ' MoreThanStop condition
'<--

Dim vAllItems As Variant
Dim Buffer() As String
Dim BufferPtr As Long
Dim Results As Worksheet
'
'  Posted by Myrna Larson
'  July 25, 2000
'  Microsoft.Public.Excel.Misc
'  Subject:  Combin
'
'
'Since you asked, here it is. It is generic, i.e. it isn't written specifically
'for a given population and set size, as yours it. It will do permutations or
'combinations. It uses a recursive routine to generate the subsets, one routine
'for combinations, a different one for permutations.
'To use it, you put the letter C or P (for combinations or permutations) in a
'cell. The cell below that contains the number of items in a subset. The Cells
'below are a list of the items that make up the population. They could be
'numbers, letters and symbols, or words, etc.
'You select the top cell, or the entire range and run the sub. The subsets are
'written to a new sheet in the workbook.
'
'
Sub ListPermutations()
  Dim Rng As Range
  Dim PopSize As Integer
  Dim SetSize As Integer
  Dim Which As String
  Dim N As Double
  Const BufferSize As Long = 4096
  Set Rng = Selection.Columns(1).Cells
  If Rng.Cells.Count = 1 Then
    Set Rng = Range(Rng, Rng.End(xlDown))
  End If
  PopSize = Rng.Cells.Count - 2
  If PopSize < 2 Then GoTo DataError
  SetSize = Rng.Cells(2).Value
  If SetSize > PopSize Then GoTo DataError
  Which = UCase$(Rng.Cells(1).Value)
  Select Case Which
  Case "C"
    N = Application.WorksheetFunction.Combin(PopSize, SetSize)
  Case "P"
    N = Application.WorksheetFunction.Permut(PopSize, SetSize)
  Case Else
    GoTo DataError
  End Select
  If N > Cells.Count Then GoTo DataError
  Application.ScreenUpdating = False
  Set Results = Worksheets.Add
  vAllItems = Rng.Offset(2, 0).Resize(PopSize).Value
  ReDim Buffer(1 To BufferSize) As String
  BufferPtr = 0
  If Which = "C" Then
    AddCombination PopSize, SetSize
  Else
    AddPermutation PopSize, SetSize
  End If
  vAllItems = 0
  Application.ScreenUpdating = True
  Exit Sub
DataError:
  If N = 0 Then
    Which = "Enter your data in a vertical range of at least 4 cells. " _
      & String$(2, 10) _
      & "Top cell must contain the letter C or P, 2nd cell is the number " _
      & "of items in a subset, the cells below are the values from which " _
      & "the subset is to be chosen."
  Else
    Which = "This requires " & Format$(N, "#,##0") & _
      " cells, more than are available on the worksheet!"
  End If
  MsgBox Which, vbOKOnly, "DATA ERROR"
  Exit Sub
End Sub

Private Sub AddPermutation(Optional PopSize As Integer = 0, _
  Optional SetSize As Integer = 0, _
  Optional NextMember As Integer = 0)
  
  Static iPopSize As Integer
  Static iSetSize As Integer
  Static SetMembers() As Integer
  Static Used() As Integer
  Dim i As Integer
  If PopSize <> 0 Then
    iPopSize = PopSize
    iSetSize = SetSize
    ReDim SetMembers(1 To iSetSize) As Integer
    ReDim Used(1 To iPopSize) As Integer
    NextMember = 1
  End If
  For i = 1 To iPopSize
    If Used(i) = 0 Then
      SetMembers(NextMember) = i
      If NextMember <> iSetSize Then
        Used(i) = True
        AddPermutation , , NextMember + 1
        Used(i) = False
      Else
        SavePermutation SetMembers()
        
        '--> ZVI: added
        If StopIt Then StopIt = False: Exit For
        '<--
        
      End If
    End If
  Next i
  If NextMember = 1 Then
    SavePermutation SetMembers(), True
    Erase SetMembers
    Erase Used
  End If
End Sub  'AddPermutation

Private Sub AddCombination(Optional PopSize As Integer = 0, _
  Optional SetSize As Integer = 0, _
  Optional NextMember As Integer = 0, _
  Optional NextItem As Integer = 0)
  Static iPopSize As Integer
  Static iSetSize As Integer
  Static SetMembers() As Integer
  Dim i As Integer
  If PopSize <> 0 Then
    iPopSize = PopSize
    iSetSize = SetSize
    ReDim SetMembers(1 To iSetSize) As Integer
    NextMember = 1
    NextItem = 1
  End If
  For i = NextItem To iPopSize
    SetMembers(NextMember) = i
    If NextMember <> iSetSize Then
      AddCombination , , NextMember + 1, i + 1
    Else
      SavePermutation SetMembers()
              
      '--> ZVI: added
      If StopIt Then StopIt = False: Exit For
      '<--
      
    End If
  Next i
  If NextMember = 1 Then
    SavePermutation SetMembers(), True
    Erase SetMembers
  End If
End Sub  'AddCombination

Private Sub SavePermutation(ItemsChosen() As Integer, _
  Optional FlushBuffer As Boolean = False)
  Dim i As Integer, sValue As String
  Static RowNum As Long, ColNum As Long
  If RowNum = 0 Then RowNum = 1
  If ColNum = 0 Then ColNum = 1
  If FlushBuffer = True Or BufferPtr = UBound(Buffer()) Then
    If BufferPtr > 0 Then
      If (RowNum + BufferPtr - 1) > Rows.Count Then
        RowNum = 1
        ColNum = ColNum + 1
        If ColNum > 256 Then Exit Sub
      End If
      Results.Cells(RowNum, ColNum).Resize(BufferPtr, 1).Value _
        = Application.WorksheetFunction.Transpose(Buffer())
      RowNum = RowNum + BufferPtr
    End If
    BufferPtr = 0
    If FlushBuffer = True Then
      Erase Buffer
      RowNum = 0
      ColNum = 0
      Exit Sub
    Else
      ReDim Buffer(1 To UBound(Buffer))
    End If
  End If
  
  '--> ZVI: added
  If vAllItems(ItemsChosen(1), 1) > MoreThanStop Then StopIt = True: Exit Sub
  '<--
  
  'construct the next set
  For i = 1 To UBound(ItemsChosen)
    sValue = sValue & ", " & vAllItems(ItemsChosen(i), 1)
  Next i
  'and save it in the buffer
  BufferPtr = BufferPtr + 1
  Buffer(BufferPtr) = Mid$(sValue, 3)
End Sub  'SavePermutation
 
Upvote 0
Hi Vladimir,
Thanks ever so much for your time and help ,The modified version of the code works with numbers "1 to 15 " however does not work with my list in post #1?
all of the lists either have 4 ,5 ,6,7 0r 8 numbers string in each cell,
any suggestion how can it be done?
Thanks
 
Upvote 0
Hi,
by changing this statement ;
Const MoreThanStop = 1 ' MoreThanStop condition
Click to expand...
to this
Const MoreThanStop ="<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=21 width=64> 1 2 6 13"</TD></TR></TBODY></TABLE> ' MoreThanStop condition
Click to expand...
the code stop after get all the combination with that cell"<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=21 width=64> 1 2 6 13</TD></TR></TBODY></TABLE>".The question is how can i include all cells starting with "1"
<TABLE style="WIDTH: 63pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=84><COLGROUP><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 63pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21 width=84> 1 2 6 13</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21> 1 3 12 14</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21> 1 4 5 8</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21> 1 7 15 16</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21> 1 9 10 11</TD></TR></TBODY></TABLE>in that statement.
thanks
 
Upvote 0
MoreThanStop constant should be numeric not the text.
It defines the maximum of the 1st number in each text series.
In case of Const MoreThanStop = 1, does the code "stops when it finish with rows starting with number "1" and not to execute the remaining rows starting with 2 ....8 etc" ?
 
Last edited:
Upvote 0
Hi Vladimir,
thanks for your help to answer your question;
In case of Const MoreThanStop = 1, does the code "stops when it finish with rows starting with number "1" and not to execute the remaining rows starting with 2 ....8 etc" ?
Click to expand...
it does not do anything with that statement but when i change "Const MoreThanStop = 1" to this "Const MoreThanStop ="
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM-COLOR: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #f0f0f0; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-RIGHT-COLOR: #f0f0f0; BORDER-LEFT-COLOR: #f0f0f0" class=xl65 height=21 width=64>1 2 6 13"</TD></TR></TBODY></TABLE>
' MoreThanStop condition it works.
But how can i include the rest of the rows starting with "1"?
thanks
<TABLE style="WIDTH: 63pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=84><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 63pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21 width=84>1 2 6 13</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; HEIGHT: 15.75pt; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21>1 3 12 14</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; HEIGHT: 15.75pt; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21>1 4 5 8</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; HEIGHT: 15.75pt; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21>1 7 15 16</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; HEIGHT: 15.75pt; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21>1 9 10 11</TD></TR></TBODY></TABLE>
 
Upvote 0
What is in your configuration cells?
I was assumed that it was as follows:
A1 = "P"
A2 = 5
A3 ="1"
A4 ="2"
A5 ="3"
...
A17 = "15"
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
537
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
902
epactheactor
E
E
  • Question Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
508
esben
E

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top