Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting rows with zero value
Hello, I've an xl file (XL2K-XP) which is an accounting file
worsheets. The sheet "Recap" have 5 colums i.e A.col=accounting items B.col=details - C.col=amounts in US$ - D.col.=amounts in foreig currencies - E.col.=amounts details About 250 accounting items are listed in col.A This file is filled by various partners with their specifics items that means that some items have no value (Zero by default). What I want is when saving the file, all the rows with zero value i C.col are deleted BUT a problem should arise when the file is save "blank" before filling it, all the rows will be deleted. How this could be managed -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting rows with zero value
Hi
don't understand the second part of your question (saved blabk) But for the first part try the following macro (put this in your workbook module: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim RowNdx As Long Dim LastRow As Long Application.ScreenUpdating = False LastRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).row For RowNdx = LastRow To 1 Step -1 if cells(RowNdx,"C").value = 0 then Rows(RowNdx).Delete end if Next RowNdx Application.ScreenUpdating = True End Sub -- Regards Frank Kabel Frankfurt, Germany Hello, I've an xl file (XL2K-XP) which is an accounting file 3 worsheets. The sheet "Recap" have 5 colums i.e A.col=accounting items - B.col=details - C.col=amounts in US$ - D.col.=amounts in foreign currencies - E.col.=amounts details About 250 accounting items are listed in col.A This file is filled by various partners with their specifics items, that means that some items have no value (Zero by default). What I want is when saving the file, all the rows with zero value in C.col are deleted BUT a problem should arise when the file is saved "blank" before filling it, all the rows will be deleted. How this could be managed ? --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting rows with zero value
Hi Frank,
I would say that my Hamburg colleague have a file name "VESSEL_Call:xls" which is the empty template and have to re-name i (when filled and ready to be mailed to me) with a vessel detail a MSCBerlin04.xls. Then after "saving as" your code will delete the row with zero value in C.col. BUT, if he first save as the blank fil BEFORE filling it, all the rows will be deleted as they all have a zer in C.col. Am I right ? Thks for all Michel Geneva "sunny -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting rows with zero value
Hi
if the rows are blank they won't be deleted. This macro searches for '0' In addition this macro only checks the used range. So if no rows are filled, no rows would be searched -- Regards Frank Kabel Frankfurt, Germany Hi Frank, I would say that my Hamburg colleague have a file named "VESSEL_Call:xls" which is the empty template and have to re-name it (when filled and ready to be mailed to me) with a vessel detail as MSCBerlin04.xls. Then after "saving as" your code will delete the rows with zero value in C.col. BUT, if he first save as the blank file BEFORE filling it, all the rows will be deleted as they all have a zero in C.col. Am I right ? Thks for all Michel Geneva "sunny" --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting rows with zero value
Hi Frank, here is the problem because the blank"template" file is loade
with zero in each cell in C. col. The routine is that each countr colleague fills the items they use, leaving zero in those they don' use. They should save the file under specific name.xls and at that tim the code will delete the rows with zero in col. C Should be the normal routine. BUT, if they save the file BEFORE filling their items, then the cod will find all rows with zero and delete them. To avoid that, I though about naming the blank file with .xlt (a template) and your code is only starting when the file is saved unde .xls Is it realistic? Thks Miche -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for deleting rows and serialising the remaing rows | Links and Linking in Excel | |||
Macro for deleting rows and serialising the remaing rows | Setting up and Configuration of Excel | |||
Macro for deleting rows and serialising the remaing rows | Excel Worksheet Functions | |||
Help!! I have problem deleting 2500 rows of filtered rows!!!! | Excel Discussion (Misc queries) | |||
deleting hidden rows so i can print only the rows showing?????? | Excel Worksheet Functions |