Insert X Rows After Specific Cell Value In Column

Aug 20, 2009

I have a dataset that i want to update, to do this I want to insert 8 rows after each row that has a value in Column C 2000. The set has 600,000 rows, comparing countries (cty, cty2) from 1980-2000. I want to make room for information up till 2008. I see lots of codes for doing this but I will also need help with actually inserting into my spread sheet.

View 3 Replies


ADVERTISEMENT

Insert Cell Contents From One Column Into Another Based On Specific Part?

Jul 30, 2014

I have a spreadsheet with four columns of text.

In column A, i have multiple levels followed by a letter (i.e. Level 1A, Level 1B etc).

In column B, i have some other details and then so on and so forth.

In column C/E/G lets say, i want to copy the information from column A to show only items that appear as "Level 1" (not "Level 1A", i only want it to check for things without the letter at the end). Then the same in column E but with "Level 2" and so on and so forth.

Column A...Column B-Column C...Column D--Column E...Column F--Column G...Column H
Level 1A....Metals----Level 1A....Metals ---Level 2A....Integral---Level 3A....Television
Level 1B....Energy----Level 1B....Energy--- Level 2B....Flowers---Level 3B....Kitchen
Level 1C....Synergy---Level 1C...Synergy--Level 2C....Full
Level 2A....Integral---Level 1D....Orders
Level 2B....Flowers
Level 1D....Orders
Level 3A....Television
Level 3B....Kitchen
Level 2C....Full

I also have data in Column B that is to do with column A (i.e "Level 1A" - "Metals") and so on with the following columns. I want the items that are in column B to also move over to column D when the things from Column A move to Column C, so at the end it will appear as below so it appears as above.

View 1 Replies View Related

Insert Today Date In Specific Cell When Any Cell In Column Changes?

Nov 12, 2013

I would like to know how to use a VBA code to insert today's date into a specific cell (B9) when any cell in column B (B2:B8) has changed.

View 3 Replies View Related

If Column Contains Data Then Insert A Blank Column And Shift Specific Column To The Right

Apr 22, 2009

What I'd like to do is; If column C contains data then insert a blank column and shift column C to the right.

View 4 Replies View Related

Format Cell To Always Insert Specific Thing When Specific Name Is Typed?

Apr 4, 2013

Is there a way to format cells to where - when you type in a certain person's first name, it will insert something entirely different?

This has seemed to have happened on a computer at work.

In Excel, when I type in "Martha" then tab, or otherwise leave that cell, Martha changes to "Cool"

If I type in anyone elses name - it stays that name. But if I type in "Martha" .. it changes to "Cool" with the bullet and the word Cool.

View 6 Replies View Related

Count Number Of Rows In Specific Column Up To Empty Cell

Feb 13, 2013

I want to count the number of rows in a specific column up to an empty cell and assign this value to a cell. I don't want to count the total number of rows but instead I want the number of the first group of rows.

For example, column A may have cells ranging from row 2 to 10 and then from row 12 to 20, so I only want to count the first group.

The below code counts the total which is not what i need.

Code:
Sub test()
Dim Mycount As Single
Mycount = Application.Count(Range("A:A"))
Cells(1, 4) = Mycount
End Sub

View 2 Replies View Related

Insert Blank Rows Indicated At Specific Points

Oct 17, 2009

I want to insert blanks rows above rows that have the number 1 inserted in column C.

I about 60,000 rows in all, doing it manually takes forever because I have to keep waiting for Excel to push the rows down every time I insert a new row.

Is there an efficient way to grab all the rows with a 1 in column C and insert rows in one swoop? If not, I will settle for any way other than manually.

View 9 Replies View Related

Can Insert Column In Specific Area And Not All The Way Through Workbook

May 3, 2013

[URL]

"Is there a way to insert a column in a workbook that doesnt run all the way through the page?" Is a page break a potential fix?

View 2 Replies View Related

Delete Rows With Specific Characters In A Specific Column

Dec 10, 2007

Currently I am using the Kickbutt VBA Find Function of Aaron, but I would like to have something that works more efficiently. What I currently do is (assuming all possible values for Column J are A - F):

Find_Range("A", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("B", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("C", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("D", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("E", Columns("J"), MatchCase:=True).EntireRow.Delete

although I just want some code that says: delete all rows except those that have "F" as content in Column J. I already tried something like:

Range("1:65536").Select
For Each cl In Range("J:J")
If cl.Text = "A" Or cl.Text = "B" Or cl.Text = "C" Or cl.Text = "D" Or cl.Text = "E" Then
Rows(cl.Row).Delete
End If
Next

but it also takes much to long. The major problem I think, is that the number of records is variable so I search the entire worksheet...

View 5 Replies View Related

Excel 2010 :: Cell Insert In One Column To Insert In Other Columns?

Mar 23, 2012

I have a master data sheet with four columns, A, B, C and D

Column A has the primary data and B,C,D has dependent data values;

So when I insert a new cell in Column A with cells Shift Down, I want mandatorily new cells to be inserted in the same row in col B, C and D as well so that data integrity is maintained;

View 2 Replies View Related

Insert Date From Column D, Into Column A, 2 Rows Up

Mar 22, 2007

Following up from Insert Blank Rows Where Date In Column D Changes from the first populated row after the inserted rows, and paste it in the 1st blank row above the populated rows (as a kind of title). How would I build this into the code below?

Private Sub CommandButton1_Click()
For x = Range("D65536").End(xlUp).Row To 7 Step -1
If Range("D" & x) <> Range("D" & x - 1) Then Range("D" & x, "D" & x + 3).EntireRow.insert
Next
End Sub

View 7 Replies View Related

How To Insert Blank Row Below A Cell Containing Specific Text

Oct 19, 2009

I was wondering if someone might be able to assist me with using VB to insert a new row below a cell containing specific text.

For example:


- All of my data is in column A

-I want to scan all of column A, and if there is a cell that contains "ACHCAMERIGROUP M", then I want a blank row inserted below it. If column A does NOT contain that text....do nothing.

View 5 Replies View Related

Insert Rows Based On The Value On Column F

Oct 31, 2009

I want to insert rows based on the coloumn F (has 6.00 on it)

206101.08.200901.08.20096.00

Since the coloumn F has value 6 on it, i want to insert 5 rows below the current row

View 9 Replies View Related

Insert Row (2) Rows Above Value Found In Column

Dec 7, 2010

I am trying to perform a simple insert macro. That will search column A for a value "Jack".

Once the value has been found insert a blank row, 2 rows up from that value. Not below.

View 9 Replies View Related

Insert Rows At A Change In A Column

May 5, 2006

I'm not really all that familiar with a lot of things in Excel. I do know how to make a macro, which this question is to help with, but I kinda skipped a lot of learning in between.

I am wanting a shortcut that will insert a new row after each change in column A. I don't think Subtotals is the answer because after I enter the row to seperate them I will want to enter another row and seperate again by column F when there is a change that is not a blank row.

View 9 Replies View Related

Minus Number From Cell And Result In Specific Cell And Specific Column

Jan 20, 2009

I have number in cell (A1) = 100. when I enter number in any cell of column(B) for example (B1)=10. then in cell (C1) the result of (A1) - (B1) = (90) and if I add in cell (B2)=10 then in cell (C1) the result of (A1) - (B1+B2) = 80 .accumulatively in cell (C1). and any number in column (B), the result will be (A1) minus any number in column(B) accumulated in (C1)

second question

I have number in cell (A1) = 100. when I enter number at cell (B1) = 10 then the result would be in the adjacent cell (C1) = (A1) - ( B1) = 90 and If I enter a new number in cell (B2) = 10 then the result would be in the adjacent cell (C2) = (A1) - (B1+B2) = 80 and If I enter a new number in cell (B3) = 10 then the result would be in the adjacent cell (C3) = (A1) - (B1+B2+B3) = 70 and so on. I want the result to be add automatically to adjacent cell in column (C)

View 3 Replies View Related

Macro To Find A Specific Word And Insert Cell

Jan 12, 2010

I have a large spreadsheet with about 18000 rows or data and about 60 columns. I need to a macro to find a specific word in a column like "charge" and anytime that word is found in the column to insert a blank cell in front of it.

Currently the sheet looks like this:Fee AChargeFeeChargeFee AChargeFeeChargeFee AChargeFee AChargeNeed to get it to look like this: (so I can sort and subtotal)Fee AChargeFee AChargeFee AChargeFee ACharge

a macro to the for whichever column I choose.

View 9 Replies View Related

Insert Rows Below Every Line That Has Data In Column A?

May 5, 2014

i have numbers in column A1:A556, i want to insert a row below every column that has a number so that they can have double spacing.

View 1 Replies View Related

Lookup Values And Insert Column And Rows?

Feb 18, 2014

I have a table (Arrears) showing a list of customers with part numbers and quantities in arrears. A second table is a customer order table showing details of what needs to be shipped for a given date.

Based on these two tables, I would like to add a column to the Order table to show the values for arrears against the order quantity for the given date. In addition if there are no order requirements but there are arrears then insert a row to show the corresponding arrears.

see the attached spreadsheet for more details. This shows the two source tables (Arrears and Order) and the expected results showing the added column for arrears and the inserted rows.

View 6 Replies View Related

Insert 2 New Rows When Column A Values Change

Jan 12, 2009

Column A of my spreadsheet consists of Subcase 1000 to Subcase 1010 for example, with up to as many as 100 repetitions of each subcase in consecutive order. See Below:

Subcase 1000
Subcase 1000
.
.
.
Subcase 1001
Subcase 1001
.
.
.
Subcase 1002
Subcase 1002
.
.
etc

I would like a macro to find where Subcase 1000 finishes and Subcase 1001 begins and then insert 2 new rows between them. These 2 new rows have to be inserted between any change found in the column A field. The searching of Column A should start at Row 15. I have uploaded an example file. This file contains 2 sheets, 'Before Macro' and 'After Macro'. Hopefully it outlines what the outcome of the macro should look like.

View 4 Replies View Related

Insert New Rows When Column Values Change

Feb 5, 2009

I've posted this thread in http://www.excelforum.com/excel-prog...ml#post2038238 but since the thread has been solved, I think nobody would look into my problem, so I'm creating a new thread for the purpose.

My previous thread:

Hi there,

I'm looking for the same idea only that I wanted the macro to select the data in the cells selected. Based on the code given by StephenR above, I tried to do some modification but I don't know how to make it to start from the first cell of my selection, not from row 15 as R_S_6 wanted. The range selection can be in any column. Here are the code that I used:

View 14 Replies View Related

Insert Blank Rows Where Date In Column D Changes

Mar 21, 2007

I have a spreadsheet containing hours logged against a specific project, all of which have a 'Week Beginning' date.

I would like a macro to insert two rows above a change of date (in the D column). How best should this be done?

It's basically a formatting macro I'm after, to group records by 'Week Beginning' date.

I've spotted a few similar requests, but none of them appeared to include the function to check for a change in date, from the cell above.

View 9 Replies View Related

Insert Rows Based On Numbers In Column

Apr 4, 2008

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!

View 5 Replies View Related

Insert Pictures Based On Cell Values From One Specific Folder

Mar 14, 2014

where i have a products list and respective pictures in a folder. What is should do is to get the relevant pictures appear on the screen when i select the relevant name.

View 1 Replies View Related

Code To Insert Rows In Next Sheet In Order Of Column B?

Apr 24, 2014

The below code used to work for me perfectly but in the last couple days it's been acting up. The code is supposed to cut out 2 rows from sheet1 and insert it in order of column B in sheet2. Up until a few days ago the code was inserting the 2, cut, rows properly but now it inserts it at the top of the sheet.

Here is the workbook I'm working with TEST.xlsm

View 1 Replies View Related

Insert Duplicate Rows Based On Numeric Value In Column

Apr 6, 2012

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?

View 5 Replies View Related

Auto Insert Rows From Another Column Based On Count

Jun 9, 2014

I would like to insert rows into column A from column D based on the count values and without affecting subsequent values in column B and C. For example. Count of a value in column A =1 and count of that same value in column D=5, THEN 5-1 (count of D - count of A) insert 4 rows in column A (shift cells down) without affecting data in column B AND C. Basically making the counts in column A and D equal.

COLUMN A
COLUMN B
COLUMN C
COLUMN D
G250001

[Code]....

The above example should look like this -

COLUMN A
COLUMN B
COLUMN C
COLUMN D

[Code]....

View 6 Replies View Related

Macro To Insert Auto Sum Function In Various Rows With A Column

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

VBA To Insert Formula And Then Fill Down Number Of Rows In Reference Column

Apr 8, 2014

I am trying to insert formulas to my cells in different columns and then fill down the formulas as many rows as in the reference column. So far I have put my formulas in a macro but I seem to get problems with " and ' symbols. My macro looks like this:

Sheets("Sold Articles Database").Select
Range("U3").Formula = " =VLOOKUP(LEFT(K3,2),'Input Variables'!$A$48:$B$52,2,FALSE)"
Range("V3").Formula = " =VLOOKUP(K3,'Product datas'!$A$2:$C$10000,3,FALSE)"
Range("W3").Formula = " =VLOOKUP(K3,'Product datas'!$A$2:$D$10000,4,FALSE)"

[Code] ........

View 3 Replies View Related

Moving Rows Into Specific Column

Sep 10, 2009

I need to move a number of rows, based on user input
in front of the first column, continuously until it 'flattens out' into one row..
And then do the procedure again for the next row
Kinda hard to explain.. maybe i'll use an example
using this matrix :

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved