Insert Rows Between Blocks Of Information

May 11, 2006

I have used some code that Kris gave me a couple of weeks ago and changed it to insert a row between each block of information. Have I got it right?

Sub InsertRows()
Dim lRow As Long
Dim r As Long
lRow = Range("A" & Rows.Count).End(xlUp).Row
Application. ScreenUpdating = False
Columns("M").Insert
[m3] = "temp1"
Range("M4:M" & lRow) = "=LEFT(A4,5)"
For r = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Cells(r, 13) <> Cells(r - 1, 13) Then Rows(r).Insert Shift:=xlDown
Next r
Columns("M:N").Delete
Application.ScreenUpdating = True

Application.DisplayAlerts = False
ActiveWorkbook. SaveAs Filename:= _
"H:HOME imcEXCELCOUPON_COUNT5_MAYREFORECAST.xls", FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.DisplayAlerts = True
ActiveWindow.SelectedSheets.PrintOut copies:=1
End Sub

View 3 Replies


ADVERTISEMENT

Insert Conditional Horizontal Page Breaks Around Logical/Visual Blocks

Apr 28, 2006

I run a report that has several blocks of information with blank rows separating them. The blocks change from time to time and across pages so they are not always in the exact same rows.

I'd like a macro that can insert page breaks around a certain number of rows (say 60), but not insert one between two rows that have any content. If the page break would fall between two filled rows I'd want it to go to the nearest blank row instead.

The pages would be about the same size but the blocks of information would not be broken up. That's my idea, anayway. I don't know VB so I can't be sure it's even possible.

Provided this is possible, I'd greatly appreciate the help, as would all the other people in my organization who have to do this on a daily basis.

View 7 Replies View Related

Excel 2013 :: Averaging Rows Of Data Within Larger Blocks Of Datasets - Skipping Rows

Jul 16, 2014

I am trying to average different rows and columns within a larger block data set in a series. This data is from a 96-well absorbance microplate reader experiment. I only mention this to describe the raw data output I am dealing with.

Each set of data is in a 12 x 8 block with the next block below it with one blank row between. So I have a block of data contained between A1->L1->L8. The next block is contained between A10->L10->L17. This continues for a total of 28 blocks.

I want to take averages from rows or columns from each block and autofill them into a single column. So for example I'll need =Average(A1-C1) with =Average(A10-C10) below it and so on and so forth. My problem is that if I try to autofill from this already started column the third row will take the average of A2-C2 instead of A19-C19.

Is there a formula/script for me to skip the correct number of rows to the next data block?

I have attached my spreadsheet to this thread. I am using Excel 2013

FeS_Kinetics.xlsx

View 1 Replies View Related

Remove Blocks Of Rows

Apr 27, 2006

I have a worksheet that collects information from another using complex formulas.

In column A I have codes i.e. PRD001A, PRD001B etc. A block of codes starts at A and can go up to Z. i.e. PRD001 could start WITH PRD001A and finish with PRD001N then there is always a PRD001W and PRD001Z to finish with.

PRD002A would follow PRD001Z and might only have A, B, W & Z codes before PRD003A.

In column K is the sum of columns D:J.

What I want is a macro that will look at column A & K and if all OR some of the codes for say PRD001 are >0 don't delete any of the rows for that group of codes.

Then look at column A & K and if all of the codes for say PRD002 =0 delete all of the rows for that group of codes.

View 9 Replies View Related

Copying Blocks Of Rows Into New Worksheets

Jul 1, 2009

What I'd like to do is to have the block of rows starting from "Agent:" and ending with "Total" copied into a separate worksheet and then have the worksheet named using the agent's name. I have highlighted the data in yellow in the attached reference file. I would like to have this done for all 58 agents. I could copy and paste manually but that wouldn't be the smartest way to go about this. I figure I probably need a looping structure for this but I am not yet proficient enough to figure this out quickly. I am supposed to have this done today.

Edit: The last part of this would be to have blocks of rows from the "sections" worksheet which use identical agent names also pasted below the data from the "questions" worksheet. I have attached the workbook I am working on now cutting and pasting manually.

View 4 Replies View Related

Summing Uneven Blocks Of Rows

Apr 21, 2006

I am trying to Sum "uneven blocks of rows" using the Sum Function.This works OK for the 1st.Block,but then the selection of cells moves down the same number of rows as the 1st Block.

I am attaching a sample file which best explains what I am trying to achieve.

View 9 Replies View Related

Move Blocks Of Data In 1 Column To Rows

Nov 15, 2006

Moving Data And Deleting Blank Rows. I am a complete newbie to excel VBA and require assistance (if at all possible). I have data in the following format in an excel sheet (I have a lot more rows but give an example for two rows)... in 1 column but I wish to separate it (i.e. move data along onto the first row in other columns) and delete blank rows.

For Example:

Col1
AAAA
BBBB
CCCC
DDDD
EEEE
AAAA
BBBB
CCCC
DDDD...............................

View 9 Replies View Related

Timestamp: Insert Into A Cell The Exact Time That The Information Was Inserted

Aug 18, 2008

I have a program that automatically exports info into excel as that info becomes available. What I need is to be able to insert into a cell the exact time that the information was inserted into excel. For example, cell A1 gets info inserted into it, I need cell B1 to automatically insert the time that the info was inserted into A1. Any time there is new info inserted into A1, B1 needs to update that corresponding time.

View 2 Replies View Related

Maro, Insert Rows Where Rows Determined By Number Of Carriage Returns

Feb 17, 2010

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

View 9 Replies View Related

Macro To Split Rows Into Groups Of 5 And Insert 3 Blank Rows In Between

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

How To Dynamically Insert Rows With Duplicate Data Of Previous Rows

Oct 30, 2013

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

View 1 Replies View Related

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).

View 6 Replies View Related

Insert Blanks Rows In Alternate Rows But Ignore If Already Blank

Jun 26, 2014

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.

View 3 Replies View Related

Exchanging Two Rows Of Information With Each Other?

Dec 11, 2012

this may seem like a relatively simple thing, but I want to take two rows of information and just switch them with each other. I want all of the formatting to stay the same though relative to each other. The problem is a color name. I have four colors of a pant that I'm tracking for production and the color order is just slightly flipped so I need to switch two of them. I don't want to have to redo the whole thing, too much info!

View 2 Replies View Related

How To Combine Rows Of Data With Like Information

May 13, 2014

I have 500 rows of data with 6 columns. Columns A,B,C,D,E are text, and column F is values.

I'm looking for a formula where I can say for any of these rows where the data in columns A through E match each other in those rows, combine them into one row of said text, with column E values totaled. I thought I could use SUMIFS for this, but I only know how to define SUMIFS with the criteria being a specific cell/text/value for which it should search on. In my example, the data in the cells can be anything, I just need rows to be "collapsed" to one row for like data.

View 2 Replies View Related

Reference Corresponding Rows Of Information Neatly?

Oct 9, 2008

I've manually inserted of what I'd like the program to be able to figure out, and a description of what I've tried, and failed at, so far.

The product codes and prices would of course all be unique, I've just mashed this together so there's some generic info, and I'm not divulging confidential.

View 2 Replies View Related

Duplicate Information To Be Removed From Different Rows?

Sep 24, 2013

I'm busy cleaning information from an excel spreadsheet. Not all the columns are populated. e.g. Row 17 will only have an email address in column E whilst row 18 will have a name, surname, company and email address (same as in row 17).

Using the remove duplicate function, removes row 18 and thus leaves me with the row that only contains the email address.

What would it take for me to be able to remove the rows that do not have the additional columns populated?

View 2 Replies View Related

Moving Rows When New Information Is Added

May 18, 2012

I have a workbook where on one sheet various peoples information is listed and based on their organization the information is then linked to one of a few different worksheets for costs. On the cost worksheets, when a new person is added to the original worksheet, their information populates the next row. At the end of the cost columns I have a the total cost of each person added together. What I need to do is have these totals continue to move down the worksheet as new people are added. Is there a way to do this in excel using VBA or another function?

View 1 Replies View Related

VBA To Add Rows Or Columns If Information Not Exist

Jul 24, 2012

I have a spreadsheet that contains overtime information for employees. Basically, it has a list of names in column A, and a list of projects in the first row, similar to this:

Name
100323
100887
100987
100987
Sum

[Code]....

At the top of the page I've inserted a section similar to this:

Name
Project Number
# Of Hours

SMITH, John
100883
5

Where the name and project number are drop down data validation lists containing all of the project numbers and employees at our facility, and the # of hours section is just a manual entry.

I require a macro that, upon clicking a button, will verify that the name is in the list and insert a row if there isn't as well as verify the project is in the row and insert a column it isn't.. then it will add the number of hours i specified in the box. It would also be nice if the list of names would sort alphabetically and the project numbers would sort numerically from left to right after any additions. As well as clear the selections and # of hours I entered in the second table shown above once everything has been entered in to the main (first) table.

I'm pretty new to creating my own macros (even though im an engineer.. )

View 1 Replies View Related

Converting Column Information Into Rows

Jan 5, 2013

I have a set of training information in the rows, with dates under it which maps back to names of employee in the left hand side. How do I convert that to Name of Employee Training attended dates expiry date.

View 8 Replies View Related

Edit Customer Information Rows

Jul 1, 2008

I am trying to develop a procedure that allows the user select a customer from a drop down list (using Data validation), thereby providing the user with a summary of customer information. That I can do. What I would like to do is allow the user to add new information in the summary section which is then appended to the customer record further down the sheet (see attached example). New data could only be added to the last two columns. The user would be required to press a form button to append the data.

The catch would be if the customer was selected again, the new data would also display in the summary section.

View 4 Replies View Related

Insert Rows Subject To Conditions In Rows Above And Below

Jun 21, 2008

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.

View 8 Replies View Related

Sorting Columns And Keep Information In Rows Intact

May 26, 2009

I am trying to sort/filter the information by columns but I need the information in each row to stay together for instance...

1 2 3 4 5
2 3 4 5 1
3 4 5 1 2
4 5 1 2 3
5 1 2 3 4

I need to sort the numbers in column 3 in ascending order or filter them but i need the entire row to move when sorted so it needs to look like this


4 5 1 2 3
5 1 2 3 4
1 2 3 4 5
2 3 4 5 1
3 4 5 1 2

I am using the list function to sort and filter the data in the columns but when i try to sort them in ascending order i get....

1 2 1 4 5
2 3 2 5 1
3 4 3 1 2
4 5 4 2 3
5 1 5 3 4

I don't know how to write formulas or VBA's or anything but am willing to try.

Is it possible to sort the data and get a result like the second set of data? and is there an easy way to make this fool proof so people that use the spreadsheet will not mess up the data?

View 8 Replies View Related

Combining Multiple Rows With Duplicate Information

Nov 24, 2009

I have a sheet with over 1000 rows showing me every employee's benefit choices. It has multiple rows for each employee - one that indicates their health option, one that indicates their dental option and so on.

I'd like to combine them so I have one row that has columns for health, dental, etc...and then their options would be listed all in the same row. I've attached the original with the solution I'd like pasted below. I'm sure this isn't difficult but I'm having trouble figuring it out.

View 2 Replies View Related

VBA To Name New TAB And Populate With Specific Wanted Information From Rows

Feb 1, 2008

1. When text is entered into a specific cell in the Master Sheet a new Tab is created named after this text and a second string of text i.e the date.

2.At the same time as this TAB is created I would like only the rows with checkboxes ticked in the Master Sheet to be copied to the new TAB(Not the Checkboxes themselves) [possibly using TRUE/FALSE in "B" row].

3.I would like the formulas, formats of these checked rows to be copied too.

4. Additionally the first 12 rows(all headers) and the last row(subtotal row)should be copied too.

The idea of the Workbook is to have an overall options sheet that is customised on a per customer basis with a record saved on a new tab in the same workbook.

View 14 Replies View Related

Copy & Repeat Single Row Information To Many Rows

Sep 3, 2008

In the excel sheet bellow that I'm attaching you will see what I'm looking for.

View 8 Replies View Related

Sum Up Duplicate Information And Delete The Un-needed Rows

May 24, 2006

I have a worksheet with duplicate but necessary information. in column A there is a Name and in column B there is a number. like...

John 43
John 6
John 23
Karen 54
Kathy 12
Kathy 23

I need to add all the numbers up for one name and then delete the un-needed rows. so it ends up

John 75
Karen 54
Kathy 35

View 5 Replies View Related

How Can I Pull Selected Rows Of Information From 1 Worksheet Page To Another

Jul 1, 2008

how to reference cells in 1 page from another and understand basic formula's, and would appreciate any input on how or if i can acheive what i'm trying to accomplish.

I have a worksheet with say 100 rows of information, it is a list of materials to go into a construction job. the columns to the right of the description calculate volume/quantity etc and produce a cost for the item. what i want to do is have a summary page that only lists the items that are being used, not all 100 items are in every job and i dont want to have to print the whole list with a bunch of empty values, which will just waste paper and ink. I'd like to enter a value in a cell or setup a check box or something that i could somehow use to then list that row or a part of that row of information on a seperate sheet, for a nice concise, easy to read list of job materials. i would like to do produce 1 sheet with the cost associated with the item, and one without cost.

View 9 Replies View Related

Searching And Editing - Add Information To Specific Rows Of Data In Worksheet

Jun 11, 2014

I am working on a project that I feel should be relatively simple but I seem to be stuck. My Goal: To add information to specific rows of data in the worksheet. I want to use a user form, and eliminate as much typing for the user as possible. I have already designed my user form, and written the code to identify the row in which I want to edit. Nonetheless, I do not know how to code the insertion of the new data. The data I want to add will be in empty cells at the end(right) of the data table.

I have attached a portion of my data set.InsertQuote.jpg

Here is where I am. The words in red are just colloquial words that I cannot seem to put into code.

Private Sub CmdInsert_Click()
Dim Company As String
Dim PartNumber As Variant
Dim Condition As String

[Code] .....

View 1 Replies View Related

Placing Numbers In Sequence Down Rows And Duplicating If Information Matches?

Mar 7, 2013

I have 2 columns,

column A has a series of 8 digit numbers (some will match some will not) sorting in A-z order from lowest to highest value.

Column B is blank at the moment.

What I want

In column B i need to add numbers starting at 1 and moving down in sequence.

If more than one rows have the same number in column A they get the same numbe rin Column B

It's really a pain or have to hand type in the numbers and I can't figure out a easy way to do this.

Attached example.

Tab 1 = Origanl Data
Tab 2 = results i'm lookin for.

View 2 Replies View Related







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