Rolling Over Data , Deleting Unwanted Rows

[TheBuGz]

Book1
	A	B	C	D	E	F	G	H
1	Sr	Date	OrderNo	PartyName	Station	Trucks	Despatched	Remaining
2	1	1/1/2004	1235	A	S	2	1	1
3	2	1/5/2004	1236	B	T	1	0	1
4	3	1/15/2004	1237	C	U	3	3	0
5	4	1/15/2004	1238	D	V	1	0	1
6	.	.	.	.	.	.	.	.
7	.	.	.	.	.	.	.	.
8	.	.	.	.	.	.	.	.
9	199	1/2/2004	1500	E	X	4	1	3
10	200	1/2/2004	1501	F	Y	1	1	0
11	201	2/2/2004	1502	G	Z	2	1	1
Sheet1

This data is in sheet 1 which keeps on growing day by day


Say in a month there may be around 1000 records.


If the balane is zero that line should be deleted in sheet 2 and the serial and the other record is updated

following is the image of the shhet I want to be shown in sheet 2


This is my first post in the Forum. Plz help me

Book1
	A	B	C	D	E	F	G	H
1	Sr	Date	OrderNo	PartyName	Station	Trucks	Despatched	Remaining
2	1	1/1/2004	1235	A	S	2	1	1
3	2	1/5/2004	1236	B	T	1	0	1
4	3	1/15/2004	1238	D	V	1	0	1
5	.	.	.	.	.	.	.	.
6	.	.	.	.	.	.	.	.
7	.	.	.	.	.	.	.	.
8	199	1/2/2004	1500	E	X	4	1	3
9	200	2/2/2004	1502	G	Z	2	1	1
Sheet2

 

[tactps]

I am not sure if this is what you want, but advise if not:

This macro (to be used on Sheet2) will look though each row and delete any rows where the "remaining" value is zero. It will stop when it finds a blank row:

Sub Delete_Nil_Values()
'
' Delete_Nil_Values Macro
'
' This macro checks for nil values and deletes the row



' If a nil value exists, then delete the row
Range("h1").Select 'This will start at cell "H1"
CellCheck = ActiveCell.Value 'Creates "CellCheck variable"

Do While CellCheck <> "" 'Loop will stop at blank row
CellCheck = ActiveCell.Value ' Makes CellCheck = whatever is in the cell
If CellCheck = 0 Then ' Looks for a zero
Selection.EntireRow.Delete ' If zero, deletes the whole row
Else
ActiveCell.Offset(1, 0).Activate 'If not zero, move down one cell
End If
Loop
End Sub

Hope that this helps

 

[TheBuGz]

Can this be done through a Formula.


I dont know VBA programming Macros


Plz help.

THEBUGZ

 

[tactps]

I don't believe that a formula would achieve the result that you want.

To set up the macro:
Press "Alt-F11"
Click on "Insert"
Click on "Module"
Copy and Paste the text I gave you exactly as it appears.
Close Microsoft Visual Basic
Save your file (in case something doesn't work properly).

To run the macro:
Ensure that you are on the sheet from which nil values are to be deleted.
Press "Alt-F8"
Click on the macro ("Delete_Nil_Values")
Click on Run

Each time you update your data and want to delete the nil values, re-run the macro.

Give it a shot.... one step at a time.

Regards

 

[PaddyD]

"Can this be done through a formula."

not & keep the "delete rows" requirement. formulas cannot change the worksheet environment (e.g. by deleting rows). anyway, why do you want to repeat so much data? one non-vba option would be to use a filter & copy over the results...

 

[TheBuGz]

Problem not splved

The original data on Sheet 2 which goes on increasing on daily basis I have to modify the data by entering the number of trucks despatched in the Despatched Column in shhet


I want the data to be copied in the from sheet 2 to shhet 1 with the rows deleted in which balance is Zero and the serial number automatically updated taking into account the deleted rows.


I think u people under stand my problem and will help me


TheBuGz

 

[tactps]

One last change to my macro:

Where I say "End Sub", replace with:

Range("A2").Select
ActiveCell.FormulaR1C1 = "=+R[-1]C+1"
Range("A2").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

This will renumber the items in the list.

 

[TheBuGz]

#value is comming when I run the Macro

 

[tactps]

Did you add in the syntax that I gave you the second time before it says "End Sub" (with "End Sub" only appearing once)?

That should fix it.

If not, paste your macro into your replay and I'll review.

Regards

 

[TheBuGz]

MY problem not solved Plz help .


Can this be done with the help of the formula instead of the macro .

I want that original data to remain the same in the sheet 1


The unwanted rows deleted and Serial numbers adjusted copied in sheet 2


I think I am not able to make u people understand my problem.


Plz help


The BuGz