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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
