# Help editing this macro so it will only use amount of cells it needs for the result

#### strat919

##### Board Regular
I have to set the range on this macro before I run it. If it would only create the amount of cells needed to complete the macro. It works well and will give results to multiple sheets, sometimes I'm getting millions of results that exceeds the row limitations. If I set it to Const maximum = 1048576 to handle any situation, it creates so many rows if not needed, and is hard to work with if I'm working with a smaller amount of rows. Thanks

Code:
``````Public Sub GetUniquePairs()
Application.ScreenUpdating = False
Const maximum = 1048576
Dim lastRow As Long, thisRow As Long
Dim i As Long, j As Long
Dim ws As Worksheet, Results As Worksheet
Dim Res(1 To maximum, 1 To 1) As Variant

Set ws = ActiveSheet
lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
thisRow = 1

For i = 1 To lastRow - 1
For j = i + 1 To lastRow
c = c + 1
Res(thisRow, 1) = ws.Cells(i, 1).Value & "," & ws.Cells(j, 1).Value
thisRow = thisRow + 1
If thisRow = maximum Then
thisRow = 1
Results.Cells(1, 1).Resize(maximum).Value = Res
Erase Res
End If
If i = lastRow - 1 Then
Results.Cells(1, 1).Resize(maximum).Value = Res
Erase Res
End If
Next j
Next i
End Sub``````

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### Yongle

##### Well-known Member
What is the calculation required to determine "the amount of cells needed" ?
- it is a function of the number of cells with coordinate values in column A
- provide VBA with that calculation

Replace
Code:
``Const maximum``
with
Code:
``Dim maximum As Long``
And then ...
Code:
``maximum = result of above calculation``
unless it exceeds 1048576
So ....
Code:
``maximum = WorksheetFunction.Min(1048576, result of above calculation)``

Last edited:

#### strat919

##### Board Regular
Thank you Yongle......I'll give that a try

Replies
10
Views
360
Replies
1
Views
61
Replies
13
Views
222
Replies
6
Views
209
Replies
1
Views
153

1,148,253
Messages
5,745,679
Members
423,967
Latest member
malayaka

### 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.

### Which adblocker are you using?

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

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