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

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/

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

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/

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