Try this method.......

Sub blanks()
Dim n As Long, iout As Long
Application.ScreenUpdating = False
iout = 1 'start of destination row
Sheets("Sheet1").Activate
For n = 5000 To 1 Step -1
If Cells(n, 2) < "" And Trim(Cells(n, 10)) = "" Then
Range(Cells(n, 1), Cells(n, 19)).Copy
Sheets("Sheet2").Cells(iout, 1).PasteSpecial Paste:=xlPasteValues
iout = iout + 1
End If
Next n
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Cheers
Nigel

"cskgg " wrote in message
...
Hi all,

Here's my problem:

- In sheets(1), identify a particular cell if it is BLANK.
(for n = 5000 to 1 step -1)
- Copy entire row (containing this particular blank cell).
- Select the next sheet (activate)
- paste the row from sheets(1) to this sheet.
(for n1 = 1 to 500)
i.e. identify the next available row before pasting.

In short, I need to display (a list in the next sheet) of all those
rows (in sheets(1)) that have a blank cell.


ActiveWorkbook.Worksheets(1).Select
For n = 5000 To 1 Step -1
If Cells(n, 2) < "" And Cells(n, 10) = "" Then
Range(Cells(n, 1), Cells(n, 19)).Select
Selection.Copy
GoTo line1
End If
Next n


line1:
ActiveWorkbook.Worksheets(2).Select
For N1 = 1 To 500
If Cells(N1, 2) < "" Then
Selection.Paste
End If
End
Next N1

I am trying this approach - but am still stuck. Appreciate your help.

Tks/Brgds
cskgg


---
Message posted from http://www.ExcelForum.com/