Hi all, I have a worksheet filled with students information (ID,name,
nationality.. etc.) and we are requested to give a print outs of
students with any information missing from his record, so my plan is
to create a macro that do the following:
- loop throgh every row until it reachs the end of the worksheet
- in each loop it will check if any of the cells is empty
- if one of the cells is empty it will copy this row to a new sheet
and then start at the beginning of the next row then continue the loop
- if none of the cells is empty (no information is missing) then go to
the beginning of the next row then continue the loop.

this is what I came up with:

Dim tmp As Integer
Do Until IsEmpty(ActiveCell)
For I = 1 To 11 ' 11 columns in each row that I have to check
Do While bolFlagged = True
If ActiveCell.Value = Empty Then
sheet2nextcell ' a function that copies the
current row to another sheet
bolFlagged = False ' I used this variable for
students not to dublicate
GoTo ExitLoop
End If
ActiveCell.Offset(0, 1).Select
Loop
ExitLoop:
Next I
Cells(ActiveWindow.RangeSelection.Row, 1).Select 'first cell
in current row
ActiveCell.Offset(1, 0).Select

as you may see this code isn't correct at all because I am no
programmer I can't figure out what I am doing wrong, so any input
would be appreciated.

One way:

Const cnCOLS As Long = 11

Dim rCell As Range
Dim rDest As Range

With Worksheets("Destination")
Set rDest = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
End With
With Worksheets("Source")
For Each rCell In .Range(.Cells(2, 1), _
.Cells(.Rows.Count, 1).End(xlUp))
With rCell.Resize(1, cnCOLS)
If Application.CountA(.Cells) < cnCOLS Then
.Copy Destination:=rDest
Set rDest = rDest.Offset(1, 0)
End If
End With
Next rCell
End With


In article .com,
wrote:

Hi all, I have a worksheet filled with students information (ID,name,
nationality.. etc.) and we are requested to give a print outs of
students with any information missing from his record, so my plan is
to create a macro that do the following:
- loop throgh every row until it reachs the end of the worksheet
- in each loop it will check if any of the cells is empty
- if one of the cells is empty it will copy this row to a new sheet
and then start at the beginning of the next row then continue the loop
- if none of the cells is empty (no information is missing) then go to
the beginning of the next row then continue the loop.

this is what I came up with:

Dim tmp As Integer
Do Until IsEmpty(ActiveCell)
For I = 1 To 11 ' 11 columns in each row that I have to check
Do While bolFlagged = True
If ActiveCell.Value = Empty Then
sheet2nextcell ' a function that copies the
current row to another sheet
bolFlagged = False ' I used this variable for
students not to dublicate
GoTo ExitLoop
End If
ActiveCell.Offset(0, 1).Select
Loop
ExitLoop:
Next I
Cells(ActiveWindow.RangeSelection.Row, 1).Select 'first cell
in current row
ActiveCell.Offset(1, 0).Select

as you may see this code isn't correct at all because I am no
programmer I can't figure out what I am doing wrong, so any input
would be appreciated.

On Apr 23, 2:32 pm, JE McGimpsey wrote:
One way:

Const cnCOLS As Long = 11

Dim rCell As Range
Dim rDest As Range

With Worksheets("Destination")
Set rDest = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
End With
With Worksheets("Source")
For Each rCell In .Range(.Cells(2, 1), _
.Cells(.Rows.Count, 1).End(xlUp))
With rCell.Resize(1, cnCOLS)
If Application.CountA(.Cells) < cnCOLS Then
.Copy Destination:=rDest
Set rDest = rDest.Offset(1, 0)
End If
End With
Next rCell
End With

In article .com,



wrote:
Hi all, I have a worksheet filled with students information (ID,name,
nationality.. etc.) and we are requested to give a print outs of
students with any information missing from his record, so my plan is
to create a macro that do the following:
- loop throgh every row until it reachs the end of the worksheet
- in each loop it will check if any of the cells is empty
- if one of the cells is empty it will copy this row to a new sheet
and then start at the beginning of the next row then continue the loop
- if none of the cells is empty (no information is missing) then go to
the beginning of the next row then continue the loop.

this is what I came up with:

Dim tmp As Integer
Do Until IsEmpty(ActiveCell)
For I = 1 To 11 ' 11 columns in each row that I have to check
Do While bolFlagged = True
If ActiveCell.Value = Empty Then
sheet2nextcell ' a function that copies the
current row to another sheet
bolFlagged = False ' I used this variable for
students not to dublicate
GoTo ExitLoop
End If
ActiveCell.Offset(0, 1).Select
Loop
ExitLoop:
Next I
Cells(ActiveWindow.RangeSelection.Row, 1).Select 'first cell
in current row
ActiveCell.Offset(1, 0).Select

as you may see this code isn't correct at all because I am no
programmer I can't figure out what I am doing wrong, so any input
would be appreciated.- Hide quoted text -

- Show quoted text -

Thank you JE McGimpsey for your answer it worked beautifully.