Macro To Insert New Rows Based On Commas In Previous Rows?
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).
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 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
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've got an excel file with over 20,000 rows with Column A being having values of a "Vendor Number" while Column B has a "Vendor Name" Here is an example:
Vendor #Vendor Name 1001ABC Supply 1002Larry, Moe, Curly Inc 1003John Smith 1004Fred, Wilma, Barney LLC
I'd like it to find the rows where the Vendor Name has a comma present, and then put it in another sheet, or in another column like column C and D. If I can't get both the Vendor # and Vendor Name re-inserted someplace, that is OK. The Vendor Name is the big one.
I need to build a macro which copies 3 rows every day and pastes the row data into an identical sheet. The three rows will have column "D" as =today(). As the days progress the three rows will change accordingly ( tag to the today's date)
e.g. 28/1/2014 28/1/2014 28/1/2014
I need the macro to recognize the date when pressed and copy the corresponding rows of data and paste them into an identical sheet with the same date. The second sheet is an archive sheet. The date will tick over as per the calendar.
I'm working on a large table which has one unique product number followed the number of rows that I want to add below it (for other variants of the same product number).
The Data looks like this:
Column B Column C 12543 2 13456 2 19543 1
I want it to look like this:
Column A Column B 12543 12543 12543 13456 13456 13456 19543 19543
I'm trying to create a macro that will ultimately insert rows based upon a particular value in a range of cells. What I would like to know if there is any way to make active only those cells that contain the letter "X"? Then I would be able to insert rows at that point. I hope I'm making this clear. An example of data is listed:
X ANDY 57.00 X DAVE 43.93 DAVE 92.00 X FRED 4.66 X GREG 23.55 GREG 84.21 GREG 8.69 X MIKE 83.50 X SETH 41.33
Want to run a script to add rows beneath each row based on the value in one of the cells in that row and then move to the next row of data and do the same.
For example:
Cell E5 = 7, I want to Insert 7 rows beneath row 5, then move to the next row and insert the number of cells equal to the value in column E of that row.
I need to create a macro that will insert exact number of rows based on the value in certain cell. e.g. value in cell F2 of sheet1 is 3...so I need to insert 3 rows down from cell F9 in sheet2 copying data in cells D9 and E9 to inserted Cells / rows.
I need to insert a row based the value of in column L. For example, beginning at L10 and down, are values. These are subtotals that are dynamic (as in they move based on how much data is inputted to the worksheet monthly.) I need to insert a row above the value. So if L22 has (2,961.25) in it, I would like to it down and insert a blank row. So on down the sheet.
I need to insert one or two rows depending on the criteria of two different columns. We have two shops (A and B)...and the sales are expresed like this: ....
Inserting Rows and Headings. Is it possible to automatically insert Rows and Headings based on the Cell value of a particular column ? For example column B consists of a field called, 'Assigned Group'. Column A consists of a field called, 'Fault description'. Column A needs to have a heading depending on the value of Column B. One row also needs to be inserted above the heading.
I have three columns - "Start Time", "End Time", and "Elapsed Time". Elapsed time is just end time - start time. What I would like to do is insert rows based on elapsed time.
For instance if start time is 6:00 and end time is 6:10, then elapsed time is 0:10. In this case I would like to insert 10 rows below. Basically I want to insert 1 line per minute (based on elapsed time).
I have a spreadsheet which contains data needing to be re-formatted into a suitable format for my database. So far I have been doing this by hand, but want to make it quicker as I have over 2000 lines to sort out. I am trying to write a macro that determines whether there is data in the column next to the active one (D2), and if there is counts how many columns there is data in. Once it has done this it needs to insert the same number of rows as there are columns, then copy the column data and transpose it into the empty rows. My macro seems to work at first, but it exits after around the 4th row, but I can't work out why.
Sub split_For_Database() Dim No_Of_Cells As Integer Dim Start_Cell As Range Dim Cell As Range Dim LastRow As Range Dim i As Integer Dim lRows As Long Set LastRow = Range("C65536").End(xlUp) i = 0 Set Start_Cell = Range("C2") Set Cell = Start_Cell Do While Start_Cell.Address <= LastRow.Address Do While Cell.Offset(0, 1) > 0 i = i + 1 Set Cell = Cell.Offset(0, 1) Loop...................................
I have a huge spreadsheet with addresses in column A and number in column B. Each address needs to have a ceirtain amount of lines inserted below, depending upon the number in column B. for example:................
With the new space inserted. the numbers i have range from 34 -2. does anyone have any code that will insert the number of rows depending on the number in column B? i HAVE had a look through the forums, but there are so many topics!
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?
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?
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: ....
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.
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.
My current code works, but there's got to be a shorter version to insert rows based on a cell value. Currently my code works on a series of If statements. If the value in the current cell is "2" then goto the next row and insert one line. If the value is "3" then go to the next row, insert, next row insert etc. I'm currently written up to a value of 10, but the coding is getting longer and longer. Anybody got a shorter loop that I could use.
I have an excel sheet that I've automated for my shop ... Basically it copies a set of rows from one sheet, asks for which cell you want to start the paste at, then pastes it... Here's what I have so far:
[Code] .....
So I tried to use a variation of strReturn to indicate number of rows wanted but that didn't work ... Basically I'm going to create the "data" to include 50 rows, but have it selectable for the user to say they only want 5, 7 or 29 rows ...
I regularly run a report that comes out in a slightly different format each time depending on whether or not there is data for specific criteria. for instance (sample attached): criteria a, b, and c are in rows, 1, 2, and 3 in columns. a, b, and c are expected to repeat themselves several times. if there is no data in one instance of b then you may see something like abcacabc.
I'd like to loop through each row in column a, check to make sure that it is the correct value (either a, b, or c) and if not, insert a blank row so I can then copy the data to my final project without having to worry about formatting.
Column B contains geographical Areas. Column C contains a list of business departments.
North Env North Env North Ops North Sales North Sales North Sales South Env South Maint South Ops South Sales South Sales South Sales
Etc.
The values and number of these departments will vary. I want to insert lines to sum the totals at the bottom of each geographic area based on the number of different departments. So, for this example, for the North three lines would be inserted. For the South, 4 lines would be inserted.
I have to create 1000 dynamic ranges and do not want to do this manually. See the list below for the syntax I am using, I am only showing the first 10.
Name Range KPI_01=OFFSET(Date,0,1) KPI_02=OFFSET(Date,0,2) KPI_03=OFFSET(Date,0,3) KPI_04=OFFSET(Date,0,4) KPI_05=OFFSET(Date,0,5) KPI_06=OFFSET(Date,0,6) KPI_07=OFFSET(Date,0,7) KPI_08=OFFSET(Date,0,8) KPI_09=OFFSET(Date,0,9) KPI_10=OFFSET(Date,0,10)
I have tried the following code which works to an extent...
Range("=OFFSET(Date,0,1)").Name = "KPI_01"
..but the final result of the named range is an actual range, i.e. "A1:A13".
How can I keep the named range as a dynamic range?
