Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to copy cell values from random series | Excel Worksheet Functions | |||
Help Pls Copy 1 Random Result To Many On Another Sheet? | Excel Worksheet Functions | |||
Random Sampling rows | Excel Discussion (Misc queries) | |||
Random Copy and Paste | Excel Programming | |||
Controlling random rows | Excel Programming |