melewie
Board Regular
- Joined
- Nov 21, 2008
- Messages
- 188
- Office Version
- 365
- Platform
- Windows
Hello all,
I am having problems trying to figure out how to do a loop in excel that copys rows to another sheet if the is a blank cell in the row. The problem I am having is some rows have more than one blank cell so it is creating duplicates, column A has unique values but having issues building this into a loop the code I am using is.
NumRows = Cells(65000, 1).End(xlUp).Row 'count number of rows to look at on raw data sheet
For ColNo = 1 To 46 ' start count column loop
For RowNo = 1 To NumRows 'start count row loop
If Cells(RowNo, ColNo).Value = "" And Cells(RowNo, 21).Value <> "External" Then
Cells(RowNo, ColNo).EntireRow.Copy
Worksheets("Missing Data").Select
Cells(65000, 1).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Worksheets("Employee Data Report").Select
End If
Next RowNo 'next row loop
Next ColNo 'all rows completed move onto next column
Which works fine but is copying over the rows with more than one blank cell once for every blank cell.
any help would be great.
Thanks
I am having problems trying to figure out how to do a loop in excel that copys rows to another sheet if the is a blank cell in the row. The problem I am having is some rows have more than one blank cell so it is creating duplicates, column A has unique values but having issues building this into a loop the code I am using is.
NumRows = Cells(65000, 1).End(xlUp).Row 'count number of rows to look at on raw data sheet
For ColNo = 1 To 46 ' start count column loop
For RowNo = 1 To NumRows 'start count row loop
If Cells(RowNo, ColNo).Value = "" And Cells(RowNo, 21).Value <> "External" Then
Cells(RowNo, ColNo).EntireRow.Copy
Worksheets("Missing Data").Select
Cells(65000, 1).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Worksheets("Employee Data Report").Select
End If
Next RowNo 'next row loop
Next ColNo 'all rows completed move onto next column
Which works fine but is copying over the rows with more than one blank cell once for every blank cell.
any help would be great.
Thanks