CODEWITHSUNDEEP
arraysexcelloopsvba
Nalk
Nalk

Reputation: 1

VBA - looping through cells using "i"

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

Upvotes: 0

Views: 292

Answers (2)

user4039065
user4039065

Reputation:

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.

Upvotes: 0

Billy Norbu
Billy Norbu

Reputation: 178

Problem is with this line. Try correcting it.

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

Upvotes: 0

Related Questions