Code To Delete Rows - Reset Running Total

Dec 6, 2012

I have a sheet with several thousand rows. I want code that will do this:

- After finding a "D:" in column A (contained in the cell but will be at a different place each row), start a running total of values in column B.
- When finding "F0" in column A...
--if the running total of values for rows that have "D:" equals the value in column B with the "F0", delete the "D:" rows
--if the running total doesn't match, reset the running total and search for the next "D:"
- Repeat for all rows.

View 1 Replies


ADVERTISEMENT

Running Total To Reset Based On Criteria

Jan 1, 2008

Have a sample file attached : sample2.xls

What I want to do is - Column H shows my current position, and column L shows the p&l.

I would like excel to calculate the p&l for each position, and put the results in column M.

For example: H3 is the opening of a new position, as H2 shows the current position as zero, and H4 is not equal to zero. The position was added to in H4, and I'm now holding 2. The entire position was sold in H5, and my current position is back to zero. I would like excel to then show in M5, the sum of H3,H4 and H5, and then start the calculation again, and keep doing so down the entire column. If there was only 3 p&l cells for each position, it wouldn't have been a problem for me to get it done, but unfortunately, they can vary quite a bit.

View 9 Replies View Related

Running Total To Reset Based On Criteria

Jan 1, 2008

Have a sample file attached, and what I want to do is as follows-

Column H shows my current position, and column L shows the p&l.

I would like excel to calculate the p&l for each position, and put the results in column M.

For example,

H3 is the opening of a new position, as H2 shows the current position as zero, and H4 is not equal to zero. The position was added to in H4, and I'm now holding 2. The entire position was sold in H5, and my current position is back to zero. I would like excel to then show in M5, the sum of H3,H4 and H5, and then start the calculation again, and keep doing so down the entire column. If there was only 3 p&l cells for each position, it wouldn't have been a problem for me to get it done, but unfortunately, they can vary quite a bit, and I have no idea how to do it

View 9 Replies View Related

Total Data- Having A VB Code To Delete Empty Rows

Mar 7, 2007

The spreadsheet I am working in has $ which I need to total. The information, or rows, can vary from day to day so there is no static place to have a cell with an @sum. Management wants me to add a pretty line that says

'Total' in one cell and the $ next to it. I have about 30 sheets with columns to total. Yuck, takes forever.

How do all of you total if you don't know the last row? At the top?

I thought it might work to put an @Sum in the last row of the spreadsheet, in the column

I need to total, and having a VB code to delete empty rows. That would be automatic.

View 9 Replies View Related

Code For Running Total Using Only 2 Cells?

Jan 15, 2012

I'm trying to create a spreadsheet that uses one cell to enter a dollar amount and stores the amount entered in another cell. But I would like the input cell be zero after each entry, and the cell storing the amounts to keep a running total.

View 4 Replies View Related

Reset Vertical Scroll And / Or Delete Unwanted Blank Rows At The Bottom?

Mar 18, 2014

My spreadsheet has 600rows and the vertical scroll bar ends at row 459424. I have attempted to delete all of the extra rows by holding SHIFT and CTRL, striking the down arrow then right-clicking on the left margin of highlighted row numbers and choosing delete.

But the unwanted rows are still there and my vertical scroll bar is not correlated to the 600 rows.

View 1 Replies View Related

Running Total Based On Content From Column For Over 1700 Rows?

Mar 26, 2014

Currently have a sheet with column A/B. Column A has any of the following letters to represent Wins, Losses, Ties, Overtime Loss, Shootout Loss - W, L, T, OTL, SOL. Column B has team point totals after each result. So after the win in row one, the team has 2 points. After the losses in rows 2 and 3, the team still has 2 points. After win in row 4 the team has 4 points. After win in row 5 the team has 6 points. After tie in row 6 team has 7 points.

W
2

L
2

L
2

W
4

W
6

T
7

What is the best formula or is there a formula to easily calculate running total based on content from column A this for over 1700 rows?

View 9 Replies View Related

Running Total From Daily Worksheet Into Monthly Total

Jun 13, 2014

I am trying to create a very basic workbook that has 2 worksheets. one is a daily input for tonnes, that then just gets cut and pasted to a different program, and the other worksheet is the running total. i.e., it adds up every time you update it.
been trying to figure out a macro so when you press the update button it then just updates the monthly total.

Colac Production.xls

View 7 Replies View Related

Delete Rows Until, Total Found In Column

Jun 24, 2009

Is there a macro that will begin in cell A2 and delete rows until the contents “Total” is found in a random row in Column A?

View 4 Replies View Related

Code For Organizing A Worksheet By Rows: Not Running

May 13, 2009

Sub SplitOut()
Dim DataSH As Worksheet, OutSH As Worksheet
Set DataSH = Sheets("Input")
DataSH.Activate
lastrow = Cells(Rows.Count, "D").End(xlUp).Row
For Each ce In Range("D2:D" & lastrow)
Application.StatusBar = "Actioning " & ce.Row & " of " & lastrow.............

I am working on this spreadsheet that has a lot of information that will only get bigger as time goes on. I would like to organize the contents of one column into separate spreadsheets. So if I have "stacking" in one cell of that column, the whole row associated with that cell will go to a new spreadsheet labeled "stacking".

I have attached the sheet. I would like column D (Type_Code) to be organized by the words in that column. So I would have 5 or so worksheets labeled stacking, cartoner, wheel, inspection, etc. The data here is input by a machine.

View 3 Replies View Related

Delete Some Rows Subtotal By Age Group Then Grand Total

Oct 21, 2011

Import Financial System -Recaps Trial BalanceFrom Date:31-Jul-2011,To Date:27-Aug-2011,
Requested By:Jenny Drumm,30-Aug-2011 9:28 AMAge Range: 0 - 30, No. of recaps: 231
Broker File NumberAgeVendor/Co/DeptFirstBankAssistsFRTBRODTYLoadsInvInvClaimsGL AcctTRUETRUEBalanceCostFeeRecptsAdjRecptsROE VarLC Var

[Code] ........

There are more age groups, & some groups have hundreds of lines. Each of the total line in the report has a number plugged in by the system, which, because of rounding, may be off by several cents. I figured out how to move the rows with Broker File Numer, etc & the row after it up above the first Age Range.

What I need to do next is:Delete all rows between "Age Range: ....." and the start of that range's data.Delete all blank rows between "Totals" and the next "Age Range: ...."Delete 5 rows after "Grand Total"Put a formula in the row immediately above each age range's "Totals" for each column, summing all the data for that column, in that age range. (Hopefully with the word Total in column B of that row, too)Put a formula in the row immediately above the "Totals" after "Grand Total" that adds together each of the Age Range totals.(I did figure out how to get the "Balance" cell in each row to sum that row going right to left, so at least I got a start! For that I'm using

Code:

LR = Range("C" & Rows.count).End(xlUp).Row
With Range("R10:R" & LR)
.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
.HorizontalAlignment = xlRight
.VerticalAlignment = xlTop
.FormulaR1C1 = "=IF(ISNUMBER(RC3), Sum(RC5:RC17), " & Chr(34) & Chr(34) & ")"
End With

View 2 Replies View Related

Code To Delete Rows Based On Status Code In Column

Jul 24, 2009

I'm trying to write a VBA script which will delete all rows in my Excel spreadsheet where Column I (which contains a status code) does not contain the word "Completed".

At the moment, I'm doing this the other way round: my script is able to search for entries in Column I which contain the status codes "Pending", "Awaiting Authorisation", "In Progress" etc and delete them. The idea is that when all those rows are deleted, I'll only be left with rows which have a status of "Completed". This works fine at the moment. However, the concern is that if a brand new status code is added to the data file, my script would be unable to pick it up and delete it. This is a small sample of the code I'm currently using (which deletes all the rows with statuses other than Completed):

View 4 Replies View Related

Add Current Total And Running Total

Oct 10, 2009

In an excel work shift schedule, I am trying to total the total number of days someone is scheduled for different shifts. I can get a total for the current schedule but I want to be able to have a year to date total as each new schedule is added in. These totals are in the AF through BC columns in the xray shift totals in the attached worksheet.

I don't know if it is possible or not and am working on a pre-existing worksheet.

View 9 Replies View Related

Excel 2013 :: Formula For Getting Job Code To Appear In Column A Of Total Rows

Mar 10, 2014

I've got general ledger information that I export out of my accounting software (see attached spreadsheet). From there, in another spreadsheet I do vlookup formulas to get information from this general ledger. However, in order for the vlookup formulas to work properly in the other spreadsheets, I have to go through this general ledger spreadsheet and manually enter just the first five numerical digits in column A for each Total row. I would like to be able to find a solution that would return just the first five characters of the category (column B found at the top of each section) into the cell in column A on each total row. I usually have to manually enter 50-100 of these many, many times a month so it gets time consuming after a while. I'm using Excel 2013.

View 1 Replies View Related

VBA Code To Delete Certain Rows

Jan 22, 2009

I have an excel file that i need to delete some empty rows in the file. it looks like this: I have to delete THE EMPTY ROW above each custom table, and leave everything else as it is.

empty row
custom table
data
data
data
empty row
custom table
data
data
data
data
empty row
custom table
data
data

View 11 Replies View Related

VBA Code To Delete Rows

Apr 30, 2014

I am looking for a vba code that will delete rows for me. have data in column E and I need the code to look for any cell that has the word "DELETE" in column E. When it finds the word "DELETE", I need it to delete the entire row. For instance if the first instance of this in cell E41120, then it should delete that entire row and look for the next instance to do the same.

I have seen codes out there to delete rows, but I can't get any of it to work for my situation.

View 7 Replies View Related

How To Delete Rows Using VBA Code

Jan 1, 2009

I have data in cells Starting from

B1= Quality, C1= Size, D1= Quantity, E1= Length, F1= Width, G1= Thickness

My data comprises from B2:D2000 ( I am exporting data from SQL Server) so the data is in the horrible conditions , having blanks cells in these three columns . Columns E , F and G are empty.

Can someone help me in cleaning these empty rows in the three columns on the following conditions

delete entire row if column B, C and D contains empty or blank cells or zero
delete entire row if any cells in Column C and D contains text values
delete the entire row if Cells in colums A contains the word Map

the data in the column C is in the following format( Length x Width x Thickness)
C2=78x36x4
C3=78.5x36.5x4.5
C4=7x4x3
C5=72x36x0.5
C6=72x36x19mm

Is it possible to get this data in the following form

E2=78,F2=36,G2=4
E3=78.5,F3=36.5,G3=4.5
E4=7,F4=4,G4=3
E5=72,F5=36,F6=0.5
E6=72,F6=36,G6=19mm

View 9 Replies View Related

Delete Rows Code

Mar 26, 2009

(excel 2007)

I am using the following piece of code, which I have copied and modified from another workbook in which it worked properly.


Option Explicit
Sub DeleteRows()

'Delete Unneeded Rows
Dim lUsedRangeRows As Long
Dim lRowCounter As Long

With ThisWorkbook.Sheets("Data")
lUsedRangeRows = .UsedRange.Rows.Count

For lRowCounter = lUsedRangeRows To 3 Step -1 'work from the bottom up
If DateValue(.Cells(lRowCounter, 5)) < DateValue(.Range("PayDate")) Then
.Cells(lRowCounter, 5).EntireRow.Delete
End If
Next lRowCounter
End With
End Sub

I receive a "Type Mismatch Error" on the If DateValue line (highlighted red). PayDate is a named range for cell A2 on the same page.

View 9 Replies View Related

VBA Code - Loop Through And Delete Rows

Oct 12, 2013

I use this code to delete some rows, how I would modify it to work in another spreadsheet where it would "Loop" through and delete rows that start with "User:Kellcyna" down to where the rolls start with "Numbers", and delete the rolls that start with "Total cost center" down to where the rolls start with "Numbers".

The data can contain up to 50000 rolls at times.

Sub Finally()
Application.ScreenUpdating = True
[a:a].AutoFilter Field:=1, Criteria1:="="
[a2:a65536].SpecialCells(xlVisible).EntireRow.Delete
If [a1] = "" Then [1:1].Delete
ActiveSheet.AutoFilterMode = False
[Code] ........

Here is a sample of the data I need the macro to work on. The rows I need deleted are the rows that are highlighted.

User: Kellcyna STANDARD HOURS BY COST CENTER Date: 09/29/2013 Time: 15:10:04 Page: 10

Comments:

Order Op Emp Post Work ctr Setup Unit Planned Earned Total Actual Actual Actual Total Total Orde C R

# Date SU Unit Plnd Stds Setup Run Tme Brd Tme Prod Run Time E

104527059 0010 00000000 09/25/2013 HSW01 0.000 0.4 HR 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 PP01

104523849 0010 00000000 09/25/2013 HSW01 0.000 0.2 HR 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 PP01

[Code] ...........

View 5 Replies View Related

Code To Delete All Empty Rows

May 20, 2014

I need a code that I can run that will delete all rows that have nothing in at the bottom of the sheet. What I mean is I will be printing a file and because rows have been deleted that had data in a lot of sheets are being printed and wasted. So what I need is a before close code or something that will actually make the last row with data in the last row!

View 2 Replies View Related

Code To Delete Empty Rows

May 7, 2007

I have a database and at times I have empty rows. I am trying to write code that looks at the database (it may change daily) and deletes the rows that are empty. The code I've written does NOT work:

Sub emptyrows()
Dim emptyrows As Object
Set emptyrows = Cells(65536, 255)
If Rows = "" Then
Selection.Delete Shift:=xlUp
End If
End Sub

View 9 Replies View Related

Code Won't Delete All Blank Rows

Sep 25, 2009

When I tried this code on excel at home it worked, but now i'm at work and it dosen't delete any rows at all!

Here's the ....

View 9 Replies View Related

Fastest Way To Delete Rows In Code

Aug 17, 2007

I have a bit of code that deletes row by row and takes a long time to process. I've seen people suggest deleting by range processes more quickly. How can I modify this to delete by range?

lngLastRow = Sheets(2).Range("A65536").End(xlUp).row
For lngRowCount = lngLastRow To 1 Step -1
If Application.WorksheetFunction. CountIf(Sheets(2).Range("A1:A" & lngRowCount), Sheets(2).Range("A" & lngRowCount)) > 1 Then
Sheets(2).Range("A" & lngRowCount).EntireRow.Delete
End If
Next

View 7 Replies View Related

Delete Duplicate Rows Code ...

Jun 4, 2008

I have trouble in deleting the duplicate rows. I have a code to find the duplicate values

Public Sub Unique_Proj() 'This is the first step which takes out the unique projects from the base data
'Call Work_Assignment
'this selects the unique projects in the sheet
Application. ScreenUpdating = False
Dim A, E, B(), n As Long
Sheets("Projects").Select
With ActiveSheet
A = . Range("g2", .Range("g" & Rows.Count).End(xlUp)).Value
Redim B(1 To UBound(A, 1), 1 To 1)
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
For Each E In A
If Not IsEmpty(E) And Not .exists(E) Then
n = n + 1: B(n, 1) = E: .Add E, Nothing
End If
Next
End With
Sheets("Unique Projects").Select
Range("G3:G" & Rows.Count).ClearContents
Range("G3").Resize(n).Value = B

End With
Application.ScreenUpdating = True
End Sub

However, this gives me a list of values in that perticular column only.

What I want is either delete the complete row which is duplicate OR select the entire range of values which are unique (based on the column searched) and paste it in a new sheet. The second option is more better for me.

View 7 Replies View Related

(worksheet).delete Method Causes Project Reset

Jun 26, 2008

I have encountered what seems like a very frustrating and stupid Excel vba bug.

When I use the worksheet.delete method. My vba project is RESET.

All forms object classes and variables disapear into the BIT BUCKET.

View 9 Replies View Related

Ratio Running Total?

Mar 28, 2014

On the attached worksheet i have blocks of data in col C in chronological order,i wish to count the number of times related data in col AG is between 1 & 3 incl (highlighted in blue) ,the result to be expressed as a % in col V,eg block A =5,col AG has 2 event between 1 & 3,so col V = 40%.I have inserted empty rows between the data for clarity,there are no empty rows in the data.

View 1 Replies View Related

Subtract From Running Total?

Aug 24, 2014

In cell A1 i have a total amount, lets say it's 20, in B1 i have a value of 5, in C1 i have 10.

Looking to see if it's possible to take the 5 from cell B1 to make A1 = 15, then 10 from cell C1 to make it 5 in A1 whilst reducing both columns B and C to zero If B1 or C1 are greater than the value in A1, A1 displays that amount that would be left

only thing i can think of is a hidden column to calculate whilst incorporating an if statement into B & C?

View 1 Replies View Related

Running Total Formula

Sep 14, 2009

I've attached a sample workbook. I'm trying to have a running total on column F once column G is completed with a ship date. The formula I have right now is =IF(G2:G55<0,SUM(F2:F22),0). but it's coming up w/zero.

View 2 Replies View Related

Calculating Running Total?

Jul 3, 2014

I want to calculate a running total (so add a new value to its original value). For example, cell A1 is the one I will be changing daily, and cell B2 is the one that I want a running total in. So if I start with A1 being 5, B2 should be 5. If the next day I type in 3 into A1, B2 should now read 8. However I want to do this for a column length, not just cells A1 & B1. So column B would be the running total of the column A values. If A2 is 3 one day and the next it is 6, B2 should on the second day read 9. And on and on down the columns.

I tried doing this without VBA using iterations, but that didn't work. Every time I did that it would add values going down, which was weird cuz none of the cells referenced the cell above it. I didn't know if they would work since I have multiple sheets in this file. (I'm only calculating the running total in one sheet, the others aren't doing anything special.) I need all the sheets in my file and I didn't know if VBA modules are sheet specific/ if you need to be sheet specific when coding.

Another question I have (since I know nothing about VBA) is how do modules work? Do you need to turn them on in the Excel spreadsheet itself? Or when you save it does it just automatically apply itself to the file its attached to?

On another note, how to do this WITHOUT VBA, that would be like 100x more fantastic.

View 7 Replies View Related

Calculate Running Total

Mar 5, 2014

I am creating an employee database. Any simple method for recording then calculating a total of occurrences when someone is tardy. I thought of using an OptionButton but I don't know how it could be applied and stored in my database. This is the code for the form as it exists now:

[Code] .....

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved