copy random rows

[monica]

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

 

[Norman Jones]

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.

 

[Guest]

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

 

[Norman Jones]

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

 

[Norman Jones]

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