Macro To Insert Rows Conditionally
Apr 8, 2014
I've got a dataset of certain trades across different markets and traders, and on different days. The date of the trade is in column A, the contract ID is column D and the trader ID is column J.
Traders do not trade every day. I would like to insert a new row/rows for each date on which traders do not trade between their first and last trade, for each market in which they participate.
Is this possible using macros?
And, if so, then I would like to insert the high and low prices for the market on those dates from a different worksheet into the newly created blank rows. On this second workbook the contract ID is column A, the date is column C, the high price is column F and the low price is column E. I would like the low price to be inserted into column AJ on the original worksheet and the high price into column AK.
View 2 Replies
ADVERTISEMENT
Nov 9, 2009
I guess I will be having a hard time explaining it so I will just try to attach a couple of excel files to illustrate it.
And this is the macro code I am using: ...
View 7 Replies
View Related
Feb 9, 2013
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:
Supplier
Invoice Date
GL Date
Invoice Amt
[Code].....
View 1 Replies
View Related
Mar 15, 2014
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).
View 6 Replies
View Related
Oct 31, 2009
I have a worksheet that includes the list of buildings in an area and the number of households in each building. I want to list the households on separate rows and to number them from 1 to x according to the total number I have. Is it possible to do this using a macro, if so, how?
View 5 Replies
View Related
Jan 18, 2010
I've created a macro to assign to a button that inserts a row above say row 10, then another macro to assign to a different button that inserts a row above row 20. My problem is, having clicked the first button a row is inserted above row 10 but of course row 20 now becomes row 21, so now I need the second button to insert a row above row 21 instead of 20.
Is there any way I can write a macro that remembers the old row numbers?
View 14 Replies
View Related
Sep 18, 2005
Example:
My sheet needs to insert a blank row under a number everytime it changes.
The column "C" may have 20 or so rows with the number 10 for example, the
next five might be the number 8, I need a row between the last number ten and
the first number eight. This may be repeated many times through the
spreadsheet.
As well, the adjacent columns "D" & "E" are the same as described above.
all 3 columns are continuous ie: ....
View 12 Replies
View Related
May 25, 2007
I need a macro that will insert 11 rows before the word total. The word total will always be merged between col A&B but will not always be in the same row. It might be in row 5 one day and row 25 the next.
View 9 Replies
View Related
Dec 2, 2008
I have recorded a macro to insert six rows. I'd like to take this one step further and have the user enter the number of rows they want to insert. Right now the data is entered in A10:F33.
View 9 Replies
View Related
Apr 7, 2009
Need to look at a number in a cell, inserts that many rows below that row, then repeats this for following rows that also have cells that indicate how many rows to insert.
Attached is a simple sample data sheet with how data looks before and how it should look after.
View 2 Replies
View Related
Apr 14, 2009
I need columns A, B, and C to have borders all the way around each cell in the row. Then, columns D through H need to be merged, with one border around the merged cell. The row height needs to be size 16 font, which I've been solving by putting in one letter that size, colored white so it doesn't show up when I print.
Basically, I have a spreadsheet of a bunch of tools, and I need to insert a row below each entry for the mechanics to sign their initials for three stages of the repair process, and also have the larger, merged cell for notes.
View 7 Replies
View Related
Nov 4, 2011
I frequently work with a data set that has about 35,000 rows and have to split it up into smaller sets. I manually insert a row after every 8,000 rows and then copy each smaller set into a new CSV file. Is it possible to automate this with a macro?
View 3 Replies
View Related
Oct 4, 2007
Column 'A' in my spreadsheet has cells with a number in and blank cells in no particular order. What I need to do is insert a new row above each number but I don't know how to write this as a macro.
View 9 Replies
View Related
Sep 19, 2003
i needed a macro that would go through the cells in column A, which is a list of adverstiser and insert a row when the name of the advertiser changed. So the macro needs to compare each cell to the one before and when the values differ, insert a macro. But I need one that will first go through column A, and then once finished with the items in A, go through column B and do the same thing. Can't get the column B to work...
View 9 Replies
View Related
Feb 19, 2010
I'm trying to figure out how to create a macro for a project at work. Basically, think of a spreadsheet with 5 tabs, but the information in Tab 1-Column D is the same in Tab-4 Column D and Tab-5 Column D. When I insert a row, though, I have to go to each tab, insert the row, and copy down the formulas from the row above to ensure the flow-through stays true. This can get very tedious.
Does anyone have a template or tips on a macro that would, in essence, work like this:
a) Highlight the row above which a row should be inserted
b) Trigger the macro
c) A row is inserted above the highlighted row in Tabs #1, #4 and #5
d) The information from the row above the inserted row is copied down to the new row in each of the three tabs.
View 6 Replies
View Related
Dec 29, 2008
My first sheet shows the data - a list of audits and their current status.
AuditStageAlphaIn ProgressBetaReportingCharlieCompleteDeltaCompleteOmegaPlanning
I've then used a simple count formula to add up the number by each stage and it's these numbers that I want the macro to use when determining how many rows to input.
Complete2Reporting1In Progress1Planning1
A second worksheet hosts the desired output - a simple report template, split into stages for each status type (completed, planning etc.) with the correct number of rows for each.
Completed AuditsCharlieDeltaReportingBetaAudits in ProgressAlphaAudits in PlanningOmega
So, I want the macro to ensure that we have the right number of rows for each stage, as this month will be run on a monthly basis and the stage each audit is at will change, and the number of rows required in each section will also be different.
View 9 Replies
View Related
Jul 13, 2009
I was searching through this site and found this macro ...
View 9 Replies
View Related
Apr 6, 2009
I would like to have a VBA code to the following:Please note that the rows are dynamic and I need the results row for every customer.Currently it is totalling and displaying the results column at the botton of every customer but I need it at the start of every customer
1.When the new customer number starts the cells are shifted and moved one row.
2.Insert the results word and it displays the results thats displayed currently at the bottom,shoudl be displayed at the top where the row gets inserted in (1)
Current view:
Customer Date of POD Del date Diff Percent
123456 03.02.2009 03.02.2009 0 100
05.02.2009 10.02.2009 5 0
Result 0
(The result is that if all the rows are 100% ,the result row is 100% else it is 0%)
Preferred View:
Customer Date of POD Del date Diff Percent
123456 Result 0
03.02.2009 03.02.2009 0 100
05.02.2009 10.02.2009 5 0
View 9 Replies
View Related
Sep 25, 2006
I need to find a way to insert a row (preferably at the bottom of an existing list) that mirrors the formatting of the row above.
I have had a stab at this (see attached example file) but my solution requires the user to select the correct activecell before it works correctly
View 9 Replies
View Related
Feb 20, 2007
I have a protected worksheet, which I have 2 macros, 1 to spell check and 1 to insert rows, they both unprotect the sheet and re protect it again once they have completed. The problem I am having is that when I protect the work sheet first time round I tick the box to allow users to insert rows, once the Macros run they disable this functionality. Is there anyway I can include this in my Macros or do I need to add a new button!
View 9 Replies
View Related
Jul 25, 2008
I am working with a spreadsheet generated from software that keeps track of fuel usage for a large fleet of vehicles. The data comes out looking like the snapshot below.
******** ******************** ************************************************************************>Microsoft Excel - June Fuel Transaction Listing.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA3=ABCDEFGH3 Transactions for CUSTOMER ID: 0000CUST7 Sales 4 5 6Product summary for Vehicle ID 00001080 7 8Product Description Transactions Quantity9 101 Unleaded 3 57.60 GL11 12Hose summary for Vehicle ID 00001080 13Site ID HoseGradeProductTransactions Quantity140001 2113 57.60 GLJune Fuel Transaction Listing [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I am trying to get the data into a more convenient format for analysis. I need a macro that will:
1) Take the text "Product summary for vehicle ID 0000****", extract the last 4 digits of the text, and paste it where the 1 is under the Product heading (a10). Those digits are the actual fleet number, and I need to separate them out from the rest of the text. The digits will change for each vehicle, so the macro should just move down the spreadsheet doing the same thing for each instance (the setup you see is repeated for every vehicle).
2) Once the first goal is accomplished, I would like the macro to then go back through and delete every row except for the rows with the pertinent data in them. So this means I would only want one row per vehicle and all rows would line up directly below each other like demonstrated below.
******** ******************** ************************************************************************>Microsoft Excel - June Fuel Transaction Listing.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA10=ABCDEFGH101080 Unleaded 3 57.60 GL111081 Unleaded 6 84.70 GL121122 Unleaded 5 47.00 GL131182 Unleaded 8 95.80 GLJune Fuel Transaction Listing [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View 9 Replies
View Related
Sep 4, 2009
I have to create a spreasheet monthly that has perhaps 60 transactions. The data is in columns A - G, with columns E and F having the costs figures. I need to sum the value in column E and also in Column F every few rows. The problem is that sometimes there are two rows per transaction and other times it could be 30 rows per transaction. Where I will be lucky is that there are two rows between transactions, and the sum goes in the first empty row.
Basically what I would need it to do, from a macro point of view is to go down column E, and every time it finds the first empty row, insert an auto sum. It would then need to ignore the next row, because it too, is empty.
Just Column E - I need an autosum in the "empty row 1's)
23.00
15.00
6.00
3.00
empty row 1
empty row 2
6.00
3.00
empty row 1
empty row 2
6.00
25.00
3.25
6.32
View 9 Replies
View Related
Aug 4, 2007
The below sheet sometimes returns blank (“”) cells in columns C:F. I’m looking for a code that hides the rows 113: to 117 when column C:F (all of them) are empty (“”) for that row. In the above example rows 115:117 should be hidden....
View 9 Replies
View Related
Apr 25, 2014
I am processing an infinite set of data from a meteorological station here in Alaska which gives me half hourly data reading with a time stamp 00.00, 00.30, 01.00, 01.30, 02.00, 02.30 and so on.
I am using this formula to detect every time half hour reading is skipped (=IF(TEXT(MOD(B1936-B1934,1),"[M]")="30","","missing")) and it works pretty well.
Still I have to check and manually insert extra missing for every half hour missing but that's bearable.
This formula inserts a "missing" every time it finds a gap.
My question is: How can I insert a row above every cell with "missing"?
How do I do that? Here is also my excel sheet.
CR1000_Meteo_20131113_2_CLEANED.xls
View 3 Replies
View Related
Sep 29, 2006
I'd like to hide rows based on the value of a specific cell (that specific cell will always be C14, and it will have up to 8 variables selectable from a drop down list).
For example, if C14 = Innovative/exploratory, i'd like to hide rows 15-45. If C14 = Technical Service, i'd like to hide rows 15-32, and 42-44. I'm not familiar with VB, but am a quick study -
View 4 Replies
View Related
Nov 22, 2007
I am trying to hide rows based on the contents of column BS. It the cell is empty (not just zero value, but completely empty of all formulae), then I want to leave it unhidden, then if the cell value is less than the value in cell E2 I want to hide the row. This code doesnt seem to like the first if...then next i
Sub hiderows()
Dim i As Long
Set v = cell.Value("$E$2")
' Turn off screen refresh for speed
Application. ScreenUpdating = False
' Get last row Col BS
For i = Range("BS2000").End(xlUp).Row To 15 Step -1
If IsEmpty(Cells(i)) Then Next i
ElseIf Cells.Value(i) < v Then Rows(i).Hidden = True
End If
Next i
' Turn on screen refresh
Application.ScreenUpdating = True
End Sub
View 4 Replies
View Related
Nov 10, 2013
I am on office 2010
I am new to VBA programming so i might be making a obvious mistake. I am trying to create a macro that will insert rows depending on a cell value. I also need this to be executed from a command button rather then to occur immediately after the cell value is entered. So as a example
Lets say
Cell A2 has a value 20
Cell A3 has a value 3
Cell A4 has a value 5
when this data is entered and then a Command button poressed there should be
20 rows beneath cell A2
3 rows beneath cell A3
5 rows beneath cell A4
View 9 Replies
View Related
Dec 19, 2013
a macro to insert rows based on certain cell values in column A.
I have uto 300 rows of data. Below is an example of column A.
R1
1
2
3
4
5
6
7
[Code]...
If (above the R) is an 8, I need to insert 2 rows above that R and directly below the 8.
If (above the R) is a 9, I need to insert 1 row above that R below, directly below the 9.
(Below the R there is always a minimum of 8 digits with the 9 and 10 being random).
I have excel 2007
View 5 Replies
View Related
May 28, 2009
I'm running a macro that opens another workbook and read data from it.How can I incorporate this code into my macro.Sorry i don't knwo VBA.
Workbooks.Open Filename:="C:Documents and SettingsmsimantbDesktopINFRACHEM_POLYMERS - DON''T DELETE.xls]Sheet1"
UserGRP_MAcro Macro
Rows("1:3").Select
Selection.Delete Shift:=xlUp
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Columns("B:E").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").EntireColumn.AutoFit
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Range("B1").Select
ActiveCell.FormulaR1C1 = "Existing userGroup"............................
View 2 Replies
View Related
May 13, 2011
I have several groupings of rows in worksheet. I need to have each separate grouping be able to automatically expand or collapse based on a value in each group.
ie-
Grouping Rows 11-15
Grouping Rows 21-25
Grouping Rows 31-36
If cell values in A10, A20, or A30 = 1, then the grouping below it would need to be collapsed, and if not, then expanded.
View 3 Replies
View Related
