deleting rows with zero value

F

frayer

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
 
F

Frank Kabel

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
 
F

frayer

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
 
F

Frank Kabel

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
 
F

frayer

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
 
Top