Rolling Over Data , Deleting Unwanted Rows

TheBuGz

Spammer
Joined
Jan 25, 2004
Messages
367
Book1
ABCDEFGH
1SrDateOrderNoPartyNameStationTrucksDespatchedRemaining
211/1/20041235AS211
321/5/20041236BT101
431/15/20041237CU330
541/15/20041238DV101
6........
7........
8........
91991/2/20041500EX413
102001/2/20041501FY110
112012/2/20041502GZ211
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
ABCDEFGH
1SrDateOrderNoPartyNameStationTrucksDespatchedRemaining
211/1/20041235AS211
321/5/20041236BT101
431/15/20041238DV101
5........
6........
7........
81991/2/20041500EX413
92002/2/20041502GZ211
Sheet2
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459
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
:biggrin:
 

TheBuGz

Spammer
Joined
Jan 25, 2004
Messages
367
Can this be done through a Formula.


I dont know VBA programming Macros


Plz help.

THEBUGZ
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

"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

Spammer
Joined
Jan 25, 2004
Messages
367
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

Well-known Member
Joined
Jan 20, 2004
Messages
3,459

ADVERTISEMENT

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.
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459
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

Spammer
Joined
Jan 25, 2004
Messages
367
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,924
Members
414,416
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top