| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to delete a row index when row is blank
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to delete a row index when row is blank
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 -- Regards Roger Govier "deepika :excel help" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to delete a row index when row is blank
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" wrote: 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 -- Regards Roger Govier "deepika :excel help" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to delete a row index when row is blank
Hi
Sheets("FTP").Activate Range(Cells(4, 1), ActiveCell.SpecialCells(xlLastCell)).EntireRow.Del ete -- Regards Roger Govier "deepika :excel help" wrote in message ... 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" wrote: 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 -- Regards Roger Govier "deepika :excel help" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Index/match - make blank cells return a blank value. | Excel Worksheet Functions | |||
| I want to index the first blank cell in a column of data | Excel Discussion (Misc queries) | |||
| "BLANK" - need to delete | Excel Worksheet Functions | |||
| Delete Blank Lines | Excel Discussion (Misc queries) | |||
| index match with blank entries | Excel Worksheet Functions | |||
![deepika :excel help[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
