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.
 
Sort by date Sort by votes
Hi Vladimir,
My configuration cells like this;
<TABLE style="WIDTH: 81pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=107><COLGROUP><COL style="WIDTH: 16pt; mso-width-source: userset; mso-width-alt: 768" width=21><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><TBODY><TR style="HEIGHT: 18.75pt" height=25><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 16pt; HEIGHT: 18.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=25 width=21> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 65pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=86>A</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 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=xl67>C</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 18.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=25 align=right>2</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 align=right>4</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 align=right>3</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=xl68> 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 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=xl68> 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 align=right>5</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=xl68> 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 align=right>6</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=xl68> 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 align=right>7</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=xl68> 1 9 10 11</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 align=right>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=xl68> 2 3 7 9</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 align=right>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=xl68> 2 4 10 12</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 align=right>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=xl68> 2 5 11 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 align=right>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=xl68> 2 8 14 15</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 align=right>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=xl68> 3 4 6 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 align=right>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=xl68> 3 5 10 15</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 align=right>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=xl68> 3 8 11 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 align=right>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=xl68> 4 7 11 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 align=right>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=xl68> 4 9 13 15</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 align=right>17</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=xl68> 5 6 9 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 align=right>18</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=xl68> 5 7 12 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 align=right>19</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=xl68> 6 7 8 10</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 align=right>20</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=xl68> 6 11 12 15</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 align=right>21</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=xl68> 8 9 12 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 align=right>22</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=xl68>10 13 14 16</TD></TR></TBODY></TABLE>
Click to expand...

And as i said before the code with your modification stops when it finish combination with the first cell, but what i need to complete all 5 cells with contain number "1" then to stop.
Thanks again and truly very much appreciate your effort.
 
Upvote 0

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
So, try this:
Rich (BB code): 

Option Explicit

'--> ZVI: added
Const MoreThanStop$ = "1"         ' MoreThanStop condition, use "" for full set
Dim StopIt As Boolean             ' Stop flag
Dim j As Long                     ' Aux variable
'<--

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 ListPermutationsOrCombinations()
  
  '--> ZVI: added
  j = Len(MoreThanStop)
  '<--
    
  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
  'construct the next set
  For i = 1 To UBound(ItemsChosen)
    sValue = sValue & ", " & vAllItems(ItemsChosen(i), 1)
  Next i
  
  '--> ZVI: added
  If j > 0 Then
    StopIt = Mid$(sValue, 3, j) > MoreThanStop
    If StopIt Then Exit Sub
  End If
  '<--
  
  'and save it in the buffer
  BufferPtr = BufferPtr + 1
  Buffer(BufferPtr) = Mid$(sValue, 3)
End Sub  'SavePermutation
Regards
 
Last edited:
Upvote 0
Hi Vladimir,
Thanks for all the help and i'm very sorry for the delay on answering,because i was unable to use web for a while....
i can insert one row a time
'--> ZVI: added
Const MoreThanStop$ = "1 2 6 13 " ' MoreThanStop condition, use "" for full set
Dim StopIt As Boolean ' Stop flag
Dim j As Long ' Aux variable
'<--
Click to expand...
but it's saving me alot of rows,instead of 4845 rows i can get only 455 rows which is fantastic.
Very very much appreciated ,Have a wonderful day .:)
 
Upvote 0
You are welcome Sezuh,
Thank you for providing feedback.
 
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