Inserting Random Rows
Jun 13, 2007
I have 2 columns of raw data. With
column one containing the document Names.
and column two containing the document titles
i.e
Column 1
Doc 1
Doc 1b
Doc 2b
Doc 3
Doc 10
Doc 11.5
Column 2
Manual 1
Manual 1
Manual 1
Manual 2
Manual 2
manual 3
Manual 4
I am looking to be able to reform this information on another sheet to look like this :-
Column 1
Manual 1
Doc 1
Doc 1b
Doc 2b
Manual 2
Doc 3
Doc 10
Manual 3
Doc 11.5
Manual 4
Now I have created an addional column on an other sheet which checks for the row number where a new manual appears. and I have made this look like the row I would like to insert.
The idea i have is to use the above Numbers and the row itself and insert it in to the formated report (working from the bottom though). With this doen for each manual name change.
the number of Documents per manual can vary....
how to write the marco for this
View 9 Replies
ADVERTISEMENT
Mar 27, 2013
I have cells (all in one column) containing text separated by commas e.g. (SD-299, SD-200, SD-300)
I am trying to transpose the text in these cells into rows.
VB:
Sub SplitAndTranspose()
Dim N() As String
N = Split(ActiveCell, ", ")
ActiveCell.Resize(UBound(N) + 1) = WorksheetFunction.Transpose(N)
End Sub
The problem with that is that when transposing it does not shift the cells down (/ insert new rows) so I lose the data already in the cells underneath.
Also, that macro would only apply to one cell I would like to be able to apply it all the data in the specific column on my Sheet.
View 3 Replies
View Related
May 25, 2013
I'm not great anyways with VBA Macro.Effectively, here is my issue. I have a spreadsheet which is really badly designed.In one column I have multiple numbers separated by a comma. I need this data separated into new individual rows, but at the same time, copying the data in the other columns in that row to the new row.
Example of what I have:
Test 1 54 email1
Test 2 32, 343, 63, 34 email2
Test 3 4934, 5342 email 3
What I need:
Test 1 54 email1
Test 2 32 email2
Test 2 343 email2
Test 2 63 email2
Test 2 34 email2
Test 3 4934 email3
Test 3 5342 email3
I have have the following code below which paste everything into a new column and into a new row, but the problem I have is that it does not push the other rows data down, nor does it copy the 1st rows data (for that data set) into the new rows created.
Code:
Option Explicit
Sub Macro1()
Dim fromCol As String
Dim toCol As String
Dim fromRow As String
Dim toRow As String
Dim inVal As String
Dim outVal As String
[Code] ........
The amount of rows I have, so this manually (text to column, then transcoding etc) it out of the questions (i have 1000 rows of this!)
View 7 Replies
View Related
Dec 31, 2008
I have an excel sheet that has the A column populated with many different things.
I'm not sure how possible it is, but I'm trying to group everything up by 7s and putting a space in between the groups.
So...
ABCDEFG1
ABCDEFG2
ABCDEFG3
ABCDEFG4
ABCDEFG5
ABCDEFG6
ABCDEFG7
HIJKLMNOP1
HIJKLMNOP2
HIJKLMNOP3
HIJKLMNOP4
HIJKLMNOP5
HIJKLMNOP6
HIJKLMNOP7
QRSTUV1
QRSTUV2
QRSTUV3
QRSTUV4
QRSTUV5
QRSTUV6
QRSTUV7
Would turn into
ABCDEFG1
ABCDEFG2
ABCDEFG3
ABCDEFG4
ABCDEFG5
ABCDEFG6
ABCDEFG7
HIJKLMNOP1
HIJKLMNOP2
HIJKLMNOP3
HIJKLMNOP4
HIJKLMNOP5
HIJKLMNOP6
HIJKLMNOP7
QRSTUV1
QRSTUV2
QRSTUV3
QRSTUV4
QRSTUV5
QRSTUV6
QRSTUV7
View 9 Replies
View Related
Jun 7, 2007
I have a workbook with over 500 rows and those rows are divided in diferent sections, to which I have left five empty rows to add more data, the problem is, how do I add four more rows once I have only one empty row of the five empty ones I had previous? There's also a formula on column H that I'd like to be copied down on to the newly added rows.
I don't know how I'm gonna deal with the rest of the other data that is gonna shift down as more rows are added, I said this because I want to be able to do the same on all the sections I have.
View 9 Replies
View Related
Aug 9, 2006
I have a 1500 row spreadsheet that will change every month. It's set up in three columns Name, Amount, Total. The Total column is supposed to hold the total of all the rows with the same name field. I.e.
4445-8889 12
4445-8889 8 20
5598-7785 10
5598-7785 10
5598-7785 20 40
The problem is that the number of identical named rows is different for each name and changes each month. I want to build a macro that will out the correct total in the last column of the last entry with the same name.
View 8 Replies
View Related
Apr 8, 2014
I have entered the current format of the data and the required format as well...
View 3 Replies
View Related
May 29, 2013
I have an excel sheet, where I want to insert a column or more columns between row C and D. The file is attached below. Usually, I would right click row D and choose "insert column". This does not work here. How would you do it?
View 2 Replies
View Related
Jun 23, 2014
How do I insert a row with A-N Merged and centered? Interior color should be dark blue with white font.
Currently I'm doing this to add a space based on a number I have in a column S.
[Code] .....
In column S I have a series of numbers 1 - 5 and when a unique number is found Insert this row I can't seem to figure out how to create.
[Code] .....
View 1 Replies
View Related
Nov 2, 2010
I am new to VBA and am trying to insert 20 blank rows to my spreadsheet. I know how to add one row, but am not sure how to add 20 rows without repeating the same line of code 20 times.
View 6 Replies
View Related
Dec 21, 2011
i have a spread sheet that has a number of different addresses in them I need a vb code that will insert 5 rows between each row of data starting from row 42 and has the possiblity of running to the end of the rows 65536. i believe the best way to do it is via column B which will always have data in it. i have tried a number of methods but they all only insert the rows once or they do it the required ammount of times one after the other so i end up with a whole section of blank rows and all my data still together.
View 4 Replies
View Related
Sep 24, 2007
I have a store report, which every now and again needs to have a new line added for a new store that has been opened. I need a inputbox to display so that you enter a "store number". Once you enter the store number, the macro adds a new line to add the store number in numberical order.
View 9 Replies
View Related
Sep 13, 2008
Looking for assistance in inserting 24 blank rows in between a column of names. And if not too much trouble have the ability to enter the number of blank rows to insert.
Before:
Column A
name1
name2
name3
etc.
After:
name1
blank row
blank row
etc.
name2
blank row
blank row
etc.
name3
blank row
blank row
etc.
View 9 Replies
View Related
Aug 8, 2006
I need to insert a new row, in a spreadsheet, after every ninth row that now exists. How is this easiest done?
View 3 Replies
View Related
Jun 4, 2007
I would like to insert rows below each of the subtotals in column D and F in the attached sheet. The current code I am using can only add rows according to one column. I don't know how to modify it so that it works on both columns. Also, when rows are added according to column D, the new inserted rows are filled with colors and I don't know how to remove. Lastly, how can I run this marco in multiple sheets?
Sub InsertRows()
Dim i As Integer
Dim rRw As Range
Set rRw = Range("D1")
For i = 1 To WorksheetFunction.CountIf(Columns(4), "* Total")
Set rRw = Columns(4). Find(What:="* Total", After:=rRw.Offset(2, 0), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If rRw.Value <> "Grand Total" Then
rRw.Offset(1, 0).EntireRow.Insert
End If
Next i
Set rRw = Nothing
End Sub
View 5 Replies
View Related
Mar 30, 2009
I need the selected rows to be displayed on a new sheet. The idea being that I have a quiz with hundreds of questions and I only want a random selection to appear when I execute the macro. I plan on hiding the original questions and only displaying the randomly selected ones.
Here is the original code from the previous post.
View 14 Replies
View Related
Apr 15, 2009
My sub is supposed to take the values from Sheet1 and paste them into 16x10 tables on Sheet2. However, for some odd reason, the loop is skipping some rows. If you take a look, there are 2184 values in Sheet1, but only 1368 end up in Sheet2...SKUs such as 518166,518167,510573,513746,513752 are just being skipped for no reason.
View 2 Replies
View Related
Oct 4, 2009
I've coded a macro that can randomly sample out rows of data from a sheet. This macro is used to sample out certain rows from a master sheet full of data (which has 48 columns) based on user names (a column titled LAST_UPDATE_NAME). For Example (ref. wkb attached), if for 5 users named Alastor, Catherine, Emma, Julie and Victor for whom i've to choose data from the Dump sheet.
I need to design a module in which if i input the no. of samples to be chosen against each name, then that many number of rows has to be chosen for that particular name and copied to a new sheet (probably such sheets can be renamed with the usernames). Also i want to consolidate the copied data from the multiple sheets and consolidate them to one sheet.
View 4 Replies
View Related
Mar 23, 2012
I have 800 rows of patient information, I need to sample 70 patients. I used the Random.org randomizer to get my 70 patients. That randomizer only gives me the line numbers such as 1, 8, 23, 45, 70, etc. I then need to go to Excel and just pick out the patients on those lines I was given above. Is there an easy way in Excel to have my spreadsheet of the 800 rows and create a (simple) calculation that will pick out the line numbers that I need all at once, so I do not have to go thru manually and choose the data from row 1, 8, 23, 45 etc to make my new spreadsheet with only my sample patients on it. OR is there a easy way to pick 70 random lines from a spreasheet..
View 1 Replies
View Related
Nov 23, 2012
I'm analysing some data sets with variable content, and need to insert blank rows every so often to provide space to do some analysis. The problem I have is that the position of the blank rows change according to the data set, I have been able to calculate how often the row needs to be inserted using VBA as part of my import routine and saved this value to a cell, but I cant figure out how to write the VBA which will insert rows using this value and stop at the end of the data set.
In the attached example I need to insert a row each time the data in column 2 (name) changes, this is the value I have calculated which in this example is 7. So in this attached file I need to insert a row in row 2, row 9 and so on etc. But this is what changes depending upon the "Product" held in colum 1.
View 1 Replies
View Related
May 4, 2014
I have some formula's that look at a range of rows
[Code] .....
The trouble is I insert rows to add new data to my sheet, this then changes the formula. $A$9 then becomes $A$10, I don't want this. I want the start of the formula to stay the same. How do I do this?
View 3 Replies
View Related
Mar 21, 2006
I am working on a database with Names and Volunteer Hours worked over about five years.
What I have is one sheet with names and info, and the respective sheets for each year and different events they worked that year.
on each year sheet I have only the first and last name, which is all that I need for personal info. I have these names on the seet using the sheet1!A1 formula. However whenever I add someone new they will not update the new row on the other sheets, they will simply ignore it. How can I make it respect my row updates?
View 3 Replies
View Related
Jan 9, 2013
Quick way of inserting the same rows into the bottom row of different tabs, the difficultly comes as the bottom row of the other tabs varies. I.e tab 2 the bottom row is row 87, tab 3 the bottom row is 53 etc.
View 1 Replies
View Related
May 2, 2014
macro for inserting rows and column in Excel.
View 1 Replies
View Related
Aug 4, 2008
Need "Copy formula when Inserting rows". For instance, A1=5, B1=10 but my formula set far behind in AE1=SUM(A1:B1). So,when I insert new row, I have to go to AE to drag the formula, is there any Macro or tips to simplify it?
View 14 Replies
View Related
Feb 23, 2009
see the attachment. You will see that I have recorded two macros:
1) To insert a new row for new people.
2) To insert a row for new animals.
With regards to 1), I would like to create a macro that copies the contents of the row with the penultimate name in the people section, and for a new row to be inserted beneath the copied cell. Similarly, with regards to 2) I would like to create a macro that copies the contents of the row with the penultimate name in the animal section, and for a new row to be inserted beneath the copied cell.
The issue that I have is that the macros do not copy the penultimate cell in each section, but copy a particular row, say row 11 each time I want to insert a new animal. So if I had inserted numerous new rows for the people section, and subsequently wanted to insert a new row for the animal section, I press “crtl+sht+a” which copies the contents of row 11 and which is not the contents of the penultimate row for the animal section.
View 2 Replies
View Related
Mar 18, 2009
I'm trying to write a macro that inserts multiple rows by giving user a MsgBox to enter the # of desired rows. Meanwhile, I need to make sure that the formulas in certain cells are filled down (not just copied from cell directly above). I put something together but it's only adding a single row. Attached is my sample workbook.
View 2 Replies
View Related
Nov 6, 2009
I've been inserting some rows in some sheets and everything is sound... Until I meet a chart! Charts in general are terrain I yet have to explore (and will be coming Monday)...
I insert a row like this: ...
View 14 Replies
View Related
Mar 27, 2012
I've written the following code to insert rows at certain points depending on certain conditions, looping through to the last row. However, as the last row number keeps moving, due to inserting rows, I am not sure how to alter the code to allow for this.
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
[Code]......
View 5 Replies
View Related
Nov 21, 2012
Below is just a part of my very long list of statement that I posted into column A, B and C of excel worksheet:
10
67.12
03-Oct-12
10
85.16
02-Oct-12
[Code] ........
Any formula or macro that can automatically change to below expectation such as inserting 3-blank rows, totaling and sorting the date.
10
85.16
2-Oct-12
10
67.12
3-Oct-12
[Code] .........
View 7 Replies
View Related
