VBA - looping through cells using "i"

0
votes

I am trying to loop through a range on cells in a column and then check if the cell is empty, if it is, I want to copy the corresponding row into a new sheet. This is what I have so far: 

    If Len(Cells(i, 17)) = 0 Then
     Sheets("GES1").Activate
     ActiveSheet.Range(Cells(i, 1), Cells(i, 17)).Select
     Selection.Copy
     Worksheets.Add after:=Sheets(Sheets.Count)
     ActiveSheet.Paste
  End If


  Next i

The problem with this code is that as soon as there is an empty cell the corresponding row gets copied and pasted into a new sheet and the following rows also get copied and pasted into new sheets even if their corresponding cells are not blank. I want the code to copy and paste any row corresponding to an empty cell value in column Q i.e 17 to a single new sheet

2
arraysexcelloopsvba
Is that all that is in that For loop, or is there something else before you reach the If statement? – Etheur
Do you want to copy each row with a blank into a new worksheet or create one new worksheet and copy all of the rows with blanks into it? – user4039065

2 Answers

0
votes

Problem is with this line. Try correcting it.

Worksheets.Add after:=Sheets(Sheets.Count)

0
votes

I believe that you want all of the rows with a blank column Q cell to be copied to a single new worksheet.

Sub copy_blank_to_new()
    Dim i As Long, ws As Worksheet

    Set ws = Worksheets.Add(after:=Sheets(Sheets.Count))

    With Sheets("GES1")
        .Cells(1, 1).Resize(1, 17).Copy Destination:=ws.Cells(1, 1)
        For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
            If Not CBool(Len(Cells(i, 17).Value)) Then _
                .Cells(i, 1).Resize(1, 17).Copy _
                  Destination:=ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        Next i
    End With

    Set ws = Nothing

End Sub

That starts by copying the first row across. I've guessed that is column header label information. It then copies each row with a blank (actually zero-length value) into subsequent rows in the same new worksheet.

If column Q could be blank then there is the distinct possibility that other columns could contain blanks. I've used column A to determine the extents of the data to be examined. If this is insufficient, there are other methods but having some idea of what your data actually looks like would help.