copy random rows
 

How can I copy all rows which has #N/A's on a sheet to
another. THe case is in a sheet i have to copy all those
rows (rowno 1, 3, 10, 33, 60,63,64...etc.) in random order
into another sheet?

how can i do this?

thanks a lot
monica

copy random rows
 

Hi Monica.

Try:

Sub Tester3()
Dim Rng As Range, rcell As Range
Dim srcRng As Range, destRng As Range
Dim srcSheet As Worksheet, destSheet As Worksheet

Set srcSheet = Worksheets("Sheet1") '<<===== CHANGE
Set destSheet = Worksheets("Sheet2") '<<===== CHANGE
Set destRng = destSheet.Range("A1") '<<===== CHANGE

On Error Resume Next
Set Rng = srcSheet.Cells. _
SpecialCells(xlFormulas, xlErrors)
On Error GoTo 0

For Each rcell In Rng
If rcell = CVErr(xlErrNA) Then
If Not srcRng Is Nothing Then
Set srcRng = Union(srcRng, rcell.EntireRow)
Else
Set srcRng = rcell
End If
End If
Next

If Not srcRng Is Nothing Then
srcRng.Copy destRng
End If
End Sub

Amend the worksheet names and the destination address to suit your needs.

---
Regards,
Norman



"monica" wrote in message
...
How can I copy all rows which has #N/A's on a sheet to
another. THe case is in a sheet i have to copy all those
rows (rowno 1, 3, 10, 33, 60,63,64...etc.) in random order
into another sheet?

how can i do this?

thanks a lot
monica

copy random rows
 

hi Morman.
thanks a lot for reponding..i tried ur code..
at this point in the end:
srcRng.Copy destRng

its giving an error "the command cannot be used on
multiple selections"
how do i rectify this error!

thanks
monica
-----Original Message-----
Hi Monica.

Try:

Sub Tester3()
Dim Rng As Range, rcell As Range
Dim srcRng As Range, destRng As Range
Dim srcSheet As Worksheet, destSheet As Worksheet

Set srcSheet = Worksheets("Sheet1") '<<===== CHANGE
Set destSheet = Worksheets("Sheet2") '<<===== CHANGE
Set destRng = destSheet.Range("A1") '<<===== CHANGE

On Error Resume Next
Set Rng = srcSheet.Cells. _
SpecialCells(xlFormulas, xlErrors)
On Error GoTo 0

For Each rcell In Rng
If rcell = CVErr(xlErrNA) Then
If Not srcRng Is Nothing Then
Set srcRng = Union(srcRng,
rcell.EntireRow)
Else
Set srcRng = rcell
End If
End If
Next

If Not srcRng Is Nothing Then
srcRng.Copy destRng
End If
End Sub

Amend the worksheet names and the destination address to
suit your needs.

---
Regards,
Norman



"monica" wrote in
message
...
How can I copy all rows which has #N/A's on a sheet to
another. THe case is in a sheet i have to copy all those
rows (rowno 1, 3, 10, 33, 60,63,64...etc.) in random
order
into another sheet?

how can i do this?

thanks a lot
monica


.

copy random rows
 

Hi Monica,

I could replicate your experience if I assigned destRng to other than a
single cell range or if I removed

.EntireRow
from the line:
Set srcRng = Union(srcRng, rcell.EntireRow)

Otherwise, the code appeared to run without error
..

---
Regards,
Norman



wrote in message
...
hi Morman.
thanks a lot for reponding..i tried ur code..
at this point in the end:
srcRng.Copy destRng

its giving an error "the command cannot be used on
multiple selections"
how do i rectify this error!

thanks
monica
-----Original Message-----
Hi Monica.

Try:

Sub Tester3()
Dim Rng As Range, rcell As Range
Dim srcRng As Range, destRng As Range
Dim srcSheet As Worksheet, destSheet As Worksheet

Set srcSheet = Worksheets("Sheet1") '<<===== CHANGE
Set destSheet = Worksheets("Sheet2") '<<===== CHANGE
Set destRng = destSheet.Range("A1") '<<===== CHANGE

On Error Resume Next
Set Rng = srcSheet.Cells. _
SpecialCells(xlFormulas, xlErrors)
On Error GoTo 0

For Each rcell In Rng
If rcell = CVErr(xlErrNA) Then
If Not srcRng Is Nothing Then
Set srcRng = Union(srcRng,
rcell.EntireRow)
Else
Set srcRng = rcell
End If
End If
Next

If Not srcRng Is Nothing Then
srcRng.Copy destRng
End If
End Sub

Amend the worksheet names and the destination address to
suit your needs.

---
Regards,
Norman



"monica" wrote in
message
...
How can I copy all rows which has #N/A's on a sheet to
another. THe case is in a sheet i have to copy all those
rows (rowno 1, 3, 10, 33, 60,63,64...etc.) in random
order
into another sheet?

how can i do this?

thanks a lot
monica


.

copy random rows
 

Hi Monica,

I could replicate your experience if I assigned destRng to other than a
single cell range or if I removed...

Should read:

I could replicate your experience if I removed...


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Monica,

I could replicate your experience if I assigned destRng to other than a
single cell range or if I removed

.EntireRow
from the line:
Set srcRng = Union(srcRng, rcell.EntireRow)

Otherwise, the code appeared to run without error
.

---
Regards,
Norman



wrote in message
...
hi Morman.
thanks a lot for reponding..i tried ur code..
at this point in the end:
srcRng.Copy destRng

its giving an error "the command cannot be used on
multiple selections"
how do i rectify this error!

thanks
monica
-----Original Message-----
Hi Monica.

Try:

Sub Tester3()
Dim Rng As Range, rcell As Range
Dim srcRng As Range, destRng As Range
Dim srcSheet As Worksheet, destSheet As Worksheet

Set srcSheet = Worksheets("Sheet1") '<<===== CHANGE
Set destSheet = Worksheets("Sheet2") '<<===== CHANGE
Set destRng = destSheet.Range("A1") '<<===== CHANGE

On Error Resume Next
Set Rng = srcSheet.Cells. _
SpecialCells(xlFormulas, xlErrors)
On Error GoTo 0

For Each rcell In Rng
If rcell = CVErr(xlErrNA) Then
If Not srcRng Is Nothing Then
Set srcRng = Union(srcRng,
rcell.EntireRow)
Else
Set srcRng = rcell
End If
End If
Next

If Not srcRng Is Nothing Then
srcRng.Copy destRng
End If
End Sub

Amend the worksheet names and the destination address to
suit your needs.

---
Regards,
Norman



"monica" wrote in
message
.. .
How can I copy all rows which has #N/A's on a sheet to
another. THe case is in a sheet i have to copy all those
rows (rowno 1, 3, 10, 33, 60,63,64...etc.) in random
order
into another sheet?

how can i do this?

thanks a lot
monica


.