John -

No reason to use the 'A' in the addresses or to .Select or .Activate. In
fact, you want to use these this as little as you possibly can.
Select and Activate will really slooow things down.

Here are 2 solutions - both get the data from user in an input box where
they enter the numbers like: 123,456,789. Spaces don't matter but they need
to be comma deliminated


this will introduce you to:
arrays, range object, do loop, application object, union worksheet
function, codename
look these up in XL help for more info

cheers - voodooJoe

Sub X1()
Dim strRows$, c%, ayRows(), rng As Range

'this one uses a redimensionable array to store row numbers and then creates
the array of rows

'using inlut box to get values - better to have code read the data if you
can
strRows = Application.InputBox("Rows - comma deliminate")
' if user put in no numbers the macro dumps. should probably check for
a cancel also
If Len(strRows) 0 Then ReDim ayRows(1 To 1) Else Exit Sub

Do
c = InStr(1, strRows, ",") 'looks for a comma in the list of rows, c
is the position of the first comma; returns 0 is no comma present
If c = 0 Then
ayRows(UBound(ayRows)) = strRows 'if no comma found c = shoe
string -- that is 1 number
Exit Do == this will always be the last number in the set so we
can exit
Else
ayRows(UBound(ayRows)) = Left(strRows, c - 1) 'if comma found,
read all# to the left
ReDim Preserve ayRows(1 To UBound(ayRows) + 1)'set the next
array elem,ent
strRows = Right(strRows, Len(strRows) - c)'trim away the
characters we read
End If
Loop

Set rng = Sheet1.Rows(ayRows(1))'set the range to something

For i = 1 To UBound(ayRows)
Set rng = Application.Union(rng, Sheet1.Rows(ayRows(i)))'set the
range to the union of the old range and the new one
Next i
rng.Copy Sheet2.Cells(1, 1)'copy andpaste

End Sub

'i like this one better.

Sub X2()
Dim strRows, rng, c%, t%

'same get value and test
strRows = Application.InputBox("Rows - comma deliminate")
If Len(strRows) = 0 Then Exit Sub

'we initially don't know the rows we want to copy
Set rng = Nothing

Do

'look for the comma
c = InStr(1, strRows, ",")

'if there is no comma, then T is the whole string (plus 1 character)
if there is a comma, we only want up to the comma
If c = 0 Then t = Len(strRows) + 1 Else t = c


If rng Is Nothing Then'this is the first row in the set where we
haven't set rng equal to anything yet
Set rng = Sheet1.Rows(Left(strRows, t - 1))
Else
Set rng = Application.Union(rng, Sheet1.Rows(Left(strRows, t -
1)))'if rng already has a value, we append the new row to the rolds ones
End If
strRows = Right(strRows, Len(strRows) - c)'trim down the string

Debug.Print rng.Address & " ... " & strRows'shows you the progress.
delete in final code

Loop While c < 0'loop; while there still are commas

rng.Copy Sheet2.Cells(1, 1)'copy paste

End Sub





"John" wrote in message
...
I am really new to VBA. I am building a tool that extracts random records
using the row numbers in Excel. The random numbers are generated by an
outside source and come in the format '123, 456, 789'

I've created a code that exctracts rows 123, 456, 789.

'///////////CODE BEGINS HERE/////////////
Range("A123,A456,A789").EntireRow.Copy
Sheets("Sample").Select
ActiveSheet.Range("A1").Select
ActiveCell.PasteSpecial (xlPasteValuesAndNumberFormats)
Application.CutCopyMode = False
End Sub
'//////////CODE ENDS HERE//////////////////

To get to this point requires a lot of massaging of the numbers in VBA
(adding the 'A' and removing the spaces) usually using global search and
replace. This is simple enough for me, but not simple enough for some end
users.

I want to use an input box or other pop-up type tool imbedded in the macro
that allows the user to input the numbers as a group (copy and paste in
one
step) in the format supplied to them (e.g. '123, 456, 789') that will
automatically convert them to the correct format (e.g. 'A123,A456,A789')
and
then update the RANGE line of the macro built above before the macro
continues past this point.

Any help will be appreciated.

Thanks,