Macro to run only in an set column/group of cells

[drhatmrexcel]

I have macro code that enters non-repeating random numbers in a user selected group of cells when the macro is run. I wish to limit the macro to only run when the user has selected only cells in column A and any of rows 7 through 206, or combination of rows in column A.
Typically the user would first select A7 and then click and drag down a number of rows as required to select cells that correspond to the number of entrants who's names are entered in cells D7 through E206 and then press the macro key combination to actually autonmatically fill the cells in column A with non-repeating random whole numbers.

The issue I am trying to keep from happening is that if the USer were to select other cells in any other colUmn other than column "A" and run the macro code it would generate random numbers in cells (columns other "A") that I don't want it to happen in.

Here is the macro code for the random number generation that I am using.


Sub InsertRandomNumbers()
'
' InsertRandomNumbers Macro
'
' Keyboard Shortcut: Ctrl+Shift+G
' Sub FillRand()
Dim nums() As Integer
Dim maxval As Integer
Dim nrows As Integer, ncols As Integer
Dim j As Integer, k As Integer
Dim Ptr As Integer
Randomize
Set s = Selection
maxval = s.Cells.Count
nrows = s.Rows.Count
ncols = s.Columns.Count
ReDim nums(maxval, 2)
'Fill the initial array
For j = 1 To maxval
nums(j, 1) = j
nums(j, 2) = Int((Rnd * maxval) + 1)
Next j
'Sort the array based on the random numbers
For j = 1 To maxval - 1
Ptr = j
For k = j + 1 To maxval
If nums(Ptr, 2) > nums(k, 2) Then Ptr = k
Next k
If Ptr <> j Then
k = nums(Ptr, 1)
nums(Ptr, 1) = nums(j, 1)
nums(j, 1) = k
k = nums(Ptr, 2)
nums(Ptr, 2) = nums(j, 2)
nums(j, 2) = k
End If
Next j
'Fill in the cells
Ptr = 0
For j = 1 To nrows
For k = 1 To ncols
Ptr = Ptr + 1
s.Cells(j, k) = nums(Ptr, 1)
Next k
Next j
End Sub

 

[VoG]

Try

Sub InsertRandomNumbers()
'
' InsertRandomNumbers Macro
'
' Keyboard Shortcut: Ctrl+Shift+G
' Sub FillRand()
Dim nums() As Integer
Dim maxval As Integer
Dim nrows As Integer, ncols As Integer
Dim j As Integer, k As Integer
Dim Ptr As Integer
Dim s As Range
Randomize
Set s = Selection
maxval = s.Cells.Count
nrows = s.Rows.Count
ncols = s.Columns.Count
If s.Column <> 1 Or ncols > 1 Or s.Row < 7 Or s.Row + nrows > 207 Then
    MsgBox "You must select within A7:A206 only"
    Exit Sub
End If
ReDim nums(maxval, 2)
'Fill the initial array
For j = 1 To maxval
nums(j, 1) = j
nums(j, 2) = Int((Rnd * maxval) + 1)
Next j
'Sort the array based on the random numbers
For j = 1 To maxval - 1
Ptr = j
For k = j + 1 To maxval
If nums(Ptr, 2) > nums(k, 2) Then Ptr = k
Next k
If Ptr <> j Then
k = nums(Ptr, 1)
nums(Ptr, 1) = nums(j, 1)
nums(j, 1) = k
k = nums(Ptr, 2)
nums(Ptr, 2) = nums(j, 2)
nums(j, 2) = k
End If
Next j
'Fill in the cells
Ptr = 0
For j = 1 To nrows
For k = 1 To ncols
Ptr = Ptr + 1
s.Cells(j, k) = nums(Ptr, 1)
Next k
Next j
End Sub

 

[drhatmrexcel]

OMG, worked perfect first try, WHOW!
I was researching on the site and didn't expect a reply fro a couple of horurs or days, and when I hit refresh you had already posted the extra code and it went right to town and did exactly what I was trying to acheive.

Thank You for your knowledge PETE!
As I don't know if you are left handed or right handed or perhaps ambidextrous, please take you predominate hand and raise it over your shoulder and pat yourself on the back for me since I can't be there to do it!

 

[drhatmrexcel]

I added code to turn off screen updating during macro execution and that drastically sped up the speed that the macro runs!

Sub InsertRandomNumbers()
'
' InsertRandomNumbers Macro
'
' Keyboard Shortcut: Ctrl+Shift+G
' Sub FillRand()
Application.ScreenUpdating = False

Dim nums() As Integer
Dim maxval As Integer
Dim nrows As Integer, ncols As Integer
Dim j As Integer, k As Integer
Dim Ptr As Integer
Randomize
Set s = Selection
maxval = s.Cells.Count
nrows = s.Rows.Count
ncols = s.Columns.Count
If s.Column <> 1 Or ncols > 1 Or s.Row < 7 Or s.Row + nrows > 207 Then
MsgBox "You must select within A7:A206 only"
Exit Sub
End If
ReDim nums(maxval, 2)
'Fill the initial array
For j = 1 To maxval
nums(j, 1) = j
nums(j, 2) = Int((Rnd * maxval) + 1)
Next j
'Sort the array based on the random numbers
For j = 1 To maxval - 1
Ptr = j
For k = j + 1 To maxval
If nums(Ptr, 2) > nums(k, 2) Then Ptr = k
Next k
If Ptr <> j Then
k = nums(Ptr, 1)
nums(Ptr, 1) = nums(j, 1)
nums(j, 1) = k
k = nums(Ptr, 2)
nums(Ptr, 2) = nums(j, 2)
nums(j, 2) = k
End If
Next j
'Fill in the cells
Ptr = 0
For j = 1 To nrows
For k = 1 To ncols
Ptr = Ptr + 1
s.Cells(j, k) = nums(Ptr, 1)
Next k
Next j
Application.ScreenUpdating = True
End Sub