how to delete a row index when row is blank

[deepika :excel help]

I have the following code that works fine, however when a task status is
compleetd and when the next task is in satrted status, the sheet 2 which
puklls data from sheet1 has a blank line whenever te statu sin sheet 1 is
completed. what shud i add inside my code so taht i don get blank rows in
sheet2.

Sub automateFTP()
'bringing in ProjectName_taskname from was into FTP

Sheets("WAS").Activate
J = 6 'WAS entries start from 6th row . so j=6
I = 4 'FTP entries start from 4th row. so i=4

While Cells(J, 4).Value <> ""

If (Cells(J, 15).Value <> "Completed") Then

Sheets("FTP").Cells(I, 3) = Cells(J, 4).Value & "_" & Cells(J, 5).Value
Sheets("FTP").Cells(I, 2) = Cells(J, 6).Value

End If

I = I + 1
J = J + 1

Wend
Sheets("FTP").Activate
End Sub

 

[Roger Govier]

Hi

Move your I = I+1 inside the If statement, so that it only gets incremented
if the Was.Cells(J,15) = "Completed".

If (Cells(J, 15).Value <> "Completed") Then
Sheets("FTP").Cells(I, 3) = Cells(J, 4).Value & "_" & Cells(J, 5).Value
Sheets("FTP").Cells(I, 2) = Cells(J, 6).Value
I = I + 1
End If

J = J + 1

 

[deepika :excel help]

Hey Roger thank you . It worked well. Now everytime when the macro is run ,
my second sheet should first empty the existing contents of the sheet 2 from
row 4 to ... row n(FTP sheet frm i=-4) and then bring in the required
entries... what kind of delete stmt should i write inside teh macro

 

[Roger Govier]

Hi

Sheets("FTP").Activate
Range(Cells(4, 1), ActiveCell.SpecialCells(xlLastCell)).EntireRow.Delete