Copy row with an empty cell for each row in a sheet
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. |
Copy row with an empty cell for each row in a sheet
On Apr 23, 2:37 pm, JE McGimpsey wrote:
See one answer in m.p.e.programming Please don't post the same question in multiple groups. It tends to fragment your answers, and potentially wastes the time of those trying to answer questions that have already been answered. 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, and I apologize for my behavior I thought my msg is going to be read by different people thats all, thanks again for your help and have a nice day. |
All times are GMT +1. The time now is 08:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com