create a macro that would insert three rows with the first row summing the values in the lower two rows in columns E to J. The third row will get a cell formatting of black fill. In addition I would like the three rows grouped with the sum row showing when the group is closed. When the macro is executed the rows will get inserted below the last row in the sheet that has a cell format fill of the color black.
There is data in rows below the last black filled row so the inserted rows will be between that data and the exiting black filled row.
Perhaps it would be useful to explain how the designer has set up this worksheet and how the users are utilizing it. After a group of rows is created or new groups added, to insert an additional row within an existing group the user selects the black filled row at the bottom of the group and inserts a new row. This would ensure the subtotal at the top row of the group captures the values in the new row because the sum formula includes the black filled row. The black filled row also serves as a separator for each group. How to improve the design and function but the creator and users are not so open.
I have a spreadsheet with 14 columns assigned to different regions. I need to remove all of the rows that have the contents of the rows under these columns as £0.00. In psydocode, the requirement is:
If Columns C:O = £0.00 Then delete
It's important that it only deletes the row if all the entries through C to O are £0.00.
Is there a way to append this code to accomidate accross all sheets?
Sub HideRows() ActiveSheet.Unprotect Password:="admin" On Error Resume Next With Range("e4:e34") .EntireRow.Hidden = False For i = 1 To .Rows.Count If WorksheetFunction. Sum(.Rows(i)) = 0 Then .Rows(i).EntireRow.Hidden = True End If Next i ActiveSheet.Protect Password:="admin" End With End Sub
The best way to explain my problem is to look at the table below:
How it looks now: ApplePrice 1 Price 2 Price 3FruitDeliciousPearStore 1 Store 2FruitVery DeliciousHow I want it to look:ApplePrice 1FruitDeliciousApplePrice 2FruitDeliciousApplePrice 3FruitDeliciousPearStore 1FruitVery DeliciousPearStore 2FruitVery Delicious
I would like to have my macro code search column A (supplier numbers) and split the rows into groups of rows of 5 or less and then insert 3 blank rows between each group of rows. The split needs to start on a new supplier number and cannot split a supplier number into two different groups. Here is a sample:
I have a spread sheet with values in the area of A1:H834
In column H, I have number values from 1-7.
Essentially that number value means that the values in the row are duplicate.
So, for example, if H2 has a value of 4, that means that $A$2:$G$2, really should have an additional 3 rows underneath with the EXACT same data in each cell, however, the way the sheet was created, was to remove the duplicate values and just indicate in column H, the number value of how many duplicates $A$2:$G$2 really is.
I need to unpackage this and create what it was originally. What type of formula can I use, to look at the value in H2, and then insert underneath that number of rowes with the exact same data as A2:G2 and do the same for the remainder of the table all the way down to A834:G834
I'm a macro novice and have been trying to teach myself how to write the correct one for a task I need to do, but I cannot seem to get it right. Basically, I have bunch of data and for one of the variables, different values are separated by commas. What I want is to create a row copying the info below for each piece of data after the comma.
Sheet1
A B C D
[Code].....
I suspect there is a fairly easy way to do this, but I cannot figure it out from searching the forums (or rather, I can't get it to work right).
i have this code which inserts blank rows in alternate rows,
Code: Sub insertrow() ' insertrow Macro Application.ScreenUpdating = True Dim count As Integer Dim X As Integer For count = 1 To 20 If activecell.Value "" Then activecell.Offset(1, 0).Select
[code].....
what changes should i make in this code to insert rows only when ther are now blank rows. So first time i run, blank rows are already there, and when i update some data at the bottom and re-run it inserts blank rows again.
as per the attached, need to insert those grey rows subject to the following condition :
if current row date <> next row date, .and. current row latitude / longitude <> next row latitude / longitude , insert grey row with date = current row date, else insert grey row next row date
note that the coordinates in the repeated grey rows, for the "Home" location, are the same through the sheet, should be entered by the user, at the beginning of the process, since there will be a spreasheet per user.
date is in column K latitude / longitude are in columns B / C
this will be of tremendous assistance in automating mileage claim review.
Deciding to try and get to grips with Excel for basic accounting, I'd just like to check some things before I start filling columns... Say in column D I have a list of names, and in column E I have a list of figures: John Smith £250 Harry Davis £350 John Smith £500 What would be the formula for finding all occurrences of John Smith, and adding up John Smith's figures to give a total? In the simple case above, the answer would be £750. Would it matter if there are any empty/blank rows in the list?
Is there a way to have a sort of programmed button that could be pressed that would insert all of the rows at once? Or perhaps a new line could be generated for each table with the only missing data being the values that would have to input manually?
To make it more clear I have attached a sample table. In the sample table, what I would want to do is insert a row at row 47 and at row 93. I want to do these at the same time easily. Keeping in mind that there are 40 some other tables arranged in series like that in one single spreadsheet. I cannot simply hit ctrl and select the two rows and then insert both rows, this would be equally as time consuming to do for all of the rows that would have to be added. Also, in the attached table, most of the values are either calculated values or are hard set numbers. The manually inputted values occur in columns B, E, and F. Everything else is copied down from the previous line.
I had fix my rows is from 1 to 100,and each ten rows consists as one row and I had use border to separate each ten rows.(Eg,row 1 to 10 is 1row).....So when I click a button and I choose to add 10rows after row 20,how can I do so? because when i choose to add 10rows after row 20,the existing row start from 21 will move down....But the problem is I had fix my rows to 100 only...How can I do so that the row 21 will move down and at the same time check the total of rows will not exceed 100?
I need to insert 2 blank rows within a spreadsheet above certain other rows that contains data that starts with a particular text string.
Details: Column A has office codes in the form of "0301A", "0301B", etc. Column F will have billing codes that could start with either "L" or "ND" or "NF".
Problem: I want to separate the L's from the ND's and also the NF's in column F based on which office they are billed to.
End result: What I want to end up with are two blank rows in between each office code in column A, then another two blank rows between the L's, ND's and NF's.
In basic terms I have column A containing a list of dates, starting from 01/01/2005 and increasing by 1 day for each row, In column B I have the value for the day. These dates and values are still being used so the number of rows will increase day by day. I would like a formula to tell me the average total for January. So it would need to SUM each January before giving the average. I realise I could prob do this with a pivot table but if someone could give me a hint for a formula that would be great.
I am trying to create a macro that will sum the total number of 1's 2's 3's 4's 5's '6s in a range of cells d17:100 and return the number of 1s to cell a3 and number of two's to cell a4 and number of 3s to cell a5 and so forth.
I also need this to run each time any changes to any cell on that particular worksheet is made - sort of like 'refresh all the sums' type of thing.
I have been working on this spreadsheet for weeks and can't get past this part!
I made a budget for my project and have to include a macro. I wanted to have the macro pop up a box for the user to input the expense they wanted summed for the year (student loan, car payment, etc) and then have the macro sum the payments from each month. I couldn't figure out how to do the pop up box either, so figured I could just do multiple macros (20 or so) but then ran into the added difficulty of needing to only use one cell out of every four.
I have columns for budgeted, actual, monthly varience and year-to- date varience and only wanted to sum the actual.) I have read the thread about summing every nth, but trying it without the macro didn't give me the same answer as adding each on a calculator. Also, trying to record a macro of just adding cells didn't work (I figured it probably wouldn't, but I tried anyways.)
I have two separate tables, one above the other, and need a way for it to automatically shift the second table down or a row between the two tables any time another row is added to the top table. Is there any way to do that?
I have a range of numbers in a single column and I want to insert a blank cell or line below each cell in the range. Is there a quick way to do this, by not using VBA.
I have a spreadsheet with data in the first few columns, then a few columns of different formulae which reference the data.
This spreadsheet is constantly getting rows inserted into it, and I'd like for the formulae to be copied into the new rows automatically, rather than having to copy/paste the formulae every time columns are inserted.
I have this excel file which has data in it. However, this data will come in everyday. Eg, A1 to A10 is QWE, A11 to A20 is RTY, A21 to 30 is UIO. But as I said earlier new data will come in everyday. For eg, it will become A1 to A15 is QWE, A16 to A30 is RTY and so and so forth.
I need to insert 2 rows after QWE, RTY, UIO. But as data will come in everyday, I cant standardise my columns to insert the 2 rows.
