Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Insert Blank Line Between Cell Value Changes


I have a spreadsheet with the following (example) :

Column A :

alabama
alabama
alabama
arizona
arizona
arizona
arkansas
arkansas

I want to insert a blank line (row) wherever the state name changes.....


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Insert Line And Copy 1 Cell From Above
I have a large spreadsheet of demographic data. In column A there is a name, in column B there is Address1 and in column C there is Address2. First I need to insert a new line under each line of data. Then I need the Address2 data (column C) of each line to be copied/pasted to that new inserted line, but in the Address1 column (column B).

View Replies!   View Related
Trimming A Cell (blank First Line From Cell)
I need to remove a blank first line from a cell, attached two cells photo. Something with replace "chr(13)" or "chr(10)".

View Replies!   View Related
Insert Blank Cell In VBA
I've a sheet and in row A is a list of tasks to do like:

A1 - Task1
A2 - Task2
A3 - ...

Is it possible in VBA to do this:

A1 - Task1
A2 - Blank cell
A3 - Task3
A4 - Blank Cell
A5 - ....

in other words to input a balnk cell between tasks (text)

View Replies!   View Related
Insert A Blank Rows Based On Cell Value
I am trying to write a macro which will insert a blank row at the end of each year(A column) (Last cell i.e Dec 95 and so on).

I have attached the file for the same ....

View Replies!   View Related
How To Insert Blank Row Below A Cell Containing Specific Text
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 Replies!   View Related
Insert Cell To Columns Having X Blank Cells In Row
I need to put an extra "spacer" cell in each row that contains either 8 or 9 blank spacer cells in columns A through H or A through I, respectively, so that everything in those rows shifts to the right by one column. The problem is that all the other rows don't need any extra spacer cells. There is no pattern to the rows that need the extra spacer cell (such as every 5th row, or something).

View Replies!   View Related
Scroll Down Column And Insert Total In Blank Cell
May seem straightforward, but not to me. I need some code to scroll down column A and insert the sum total of A2:A16 in B17, and repeat this everytime a blank cell appears in colum B, continuing down to the end of the worksheet.

View Replies!   View Related
Insert Line Between Months
In sheet colA have dates how to insert lines between months. eg 3/19,4/5

View Replies!   View Related
Insert A Line Of Text
Simple question: Can an Excel Macro insert a line of boilerplate text under the last thing copied to a worksheet? I want to paste something into a worksheet, and add a message under it, and a blank line.

View Replies!   View Related
Insert Formula In Every Line
I have the code below in a userform. I would like to add a line that will insert this formula in column B everytime a new row is inserted....

View Replies!   View Related
Insert Line & Update Macro
I have created this macro below however if on the worksheet I insert a line or lines the macro range G25:G107 does not change - it remains static.

How can I get an insert of a line to increase the macro range by the number of inserted lines. For example if I insert 2 lines then the macro range s/b now G25:G109.

View Replies!   View Related
Line Insert After A Change In Column H (VBA)
I currently have a macro that does several thing, i want to add another task.

I need a row inserted everytime there is a change in column H from "0" to "F" only. The infromation will always start in row 8 but the last row is not always the same, it will be different every time.

ABCDEFGHI112345678223456783#N/A#N/A#N/A#N/A#N/A#N/A#N/A45B=Bi-WeeklyID type 2ID Type 308 GAPamount due6HCCompanyS= Semi-MonthlyCO#NAMEFORMER (F) or Current (0)78546WalreBbr55a234amy01009524WalreSn32m56daniel05001041WalreS589653jode0100011945WalreBef343456caryF2001212WalreSg5365M5665mikeF40013Grand Total2,200.00

View Replies!   View Related
VBA - Insert New Line At Top Of Predefined Table
I currently have a 200 row table spanning 10 columns. There is one numeric column and the rest are free text. The table charts the different contents on one of the shared drives here at work. The drive is frequently in use with things being added and removed from it.

I have an excel table already which can sort the contents in order of size (numeric row) or name of the folder (the first text column). I have two buttons at the top.
The first inserts a row, which I did by recording a macro and then copying the code into the button.

The second I would like to do the following: When a user selects a cell in a row, the button, then clicked, deletes the entire row. Not just the data in it, but excises the row itself and stitches the surrounding materials together. The code to delete a row I could obtain easily, but I can't work out to say "find the selected cell, and take out the row it belongs to".

View Replies!   View Related
Multiple Line Insert With Macro
I have two sheets that i am working with sheet1 and sheet2.

I need a macro that will insert multiple rows in row 8 of sheet 1.

I also need it to copy the formula from row 8 sheet1 to every new row inserted.

the number of rows inserted will depend on colum A from sheet2.

*column A contains numbers, and the amount of numbers will vary. The data starts in A11 and continues down.

So for example. if sheet 2 column A11 and A12 and A13 are the only cells that contain numbers, then the macro will add 3 lines starting in row 8 of sheet1 and it will also copy the formulas from that row to every new row inserted.

View Replies!   View Related
Insert 2 Lines Under Line 6 In The Active Sheet
I have to do a large amount of routine work with a large number of sheets. I have tried to record some macros to do the job.

First of all I would like to insert 2 lines under line 6 in the active sheet.

I then need to drag the formulas found in line 6 down over the two new lines.

Thirdly i would like to change the text string "xxx" in the formulas found in line 7 to "yyy"


View Replies!   View Related
Insert A Line On New Worksheet With Copied Data
I need to add a line on the top of a new worksheet with the data copied from another worksheets cells. Using a macro. The line has to be created everytime on the top of the new excel sheet with the previous data moving one down.

View Replies!   View Related
Insert Line Feed At Intervals In A Phrase
Each cell in Range("A1:A2000") contains a remark, each phrase or remark is
Between 5 & 70 characters all written without line feeds (carriage return,i mean Alt Enter) Just spaces between words. What I would like to do in every cell is to force a line feed (Alt Enter) every 10 characters, and if the 10th character happens to be in the middle of a word I want the line feed to be inserted at the end of this word. Note that the phrases are not necessarily multiples of 10.

View Replies!   View Related
Macro To Insert A Line And Duplicate Data
Worksheet excel with multiple lines and colums. I need a macro to automatically insert a new Line where the cursor is located, and duplicate data from previous cells: let say cursor is located L10, macro will insert L11, and duplicate L10 -C1 data to L11 -C1. I have a macro that does this function, but the Line insertion is always at the same line.May be the macro should "read" first the cursor position, Lx and then move down +1 line prior to duplicate the data.

View Replies!   View Related
Blank Top Line - ComboBox
I have a ComboBox on a UserForm which is populated with data from a spreadsheet. When I run the program I always get a blank line at the top. Once I select an item from the drop down list the blank line goes away. Is there any way to get the ComboBox to initially display the first item of my list, i.e. no blank line.

View Replies!   View Related
Open A Blank Line Between The Numbers
Col AB has numbers from xero to 110 in descending order. I need a macro that will open a blank line between the numbers 29 and 30.

View Replies!   View Related
Submit Button Next Blank Line
I have the following code that works just fine half of what I need it to do. The line that adds the text in column A functions correctly but the code for column B fails because column B is empty by default. The goal is to have the information in column B on the same row as column A. As well as adding two more cells on the same row as column A. The two new columns would be E and G and should be in the same row as the information from Column A. with formulas copied from e2 and g2.

View Replies!   View Related
Blank Line Break In Message Box
I have created a message box and I want leave two blank lines in the middle of the text. Is this possible?

At the moment my code for the message box line looks like:

Msg = "This process will print each site for " & UserSlection & ". Before continuing please ensure you have the right printer selected. (I.E. a colour printer where available). The process may take several minutes to complete. Do you wish to continue?"

I just want to move the "Do you wish to continue?" down two lines so that it is easier to read.

View Replies!   View Related
Group Part Numbers, Blank Line Btwn Unlike
i have a spreadsheet with 21,000 part numbers. I am trying to group the like part numbers, then leave a space between the unlike part numbers. right now my spreadsheet has a space between each part number and i want to eliminate that. but also keeping the part's qty, date, etc. with it.

View Replies!   View Related
Insert Blank Rows?
I have a range of numbers in a single column and I want to insert a blank cell or line below each cell in the range. Is there a quick way to do this, by not using VBA.

View Replies!   View Related
Insert A Blank New Row If Check The Different Content In The Next Row
i attach my sample excel file here, book1.xls. sheet1, "input". it check the column "C" row by row and if it detect the different content data in next column, it will auto insert a new row. it will ignore the space. below is my coding:

View Replies!   View Related
Find Changes In Data And Insert Blank Row
Need a VBA macro that is similar to the subtotals function but without the subtotals? Basically I want the macro to look at column A and say, for each change in column A insert a blank row… I don’t need to worry about the sorting, I just would like to know how I can tell a spreadsheet to look at a column find changes in that column and just insert a blank row. On the second page of the attached spreadsheet is an example of the desired output… I will probably use this for many different spreadsheets so the simpler and more versatile the better, ie I can change the column I want the macro to look at.

View Replies!   View Related
Insert Blank Rows Between Duplicates
but this time, the numers of duplicates is variable:
before:

---A
1-6
2-6
3-6
4-1
5-1
6-9
7-9
8-9
9-9
ETC.

and after I want to:

---A
1--6
2--6........

View Replies!   View Related
Insert Blank Rows, Sort And Highlight
I am trying to accomplish three things with the attached workbook. The first thing is the insert a blank row between the different part numbers, column "A" to make the sheet easier to read. The second thing is to highlight the data in light gray (A:E) leaving the blank row that was created empty. The third is to then sort the worksheet by data in column "A" to group like numbers together.

View Replies!   View Related
Insert Blank Rows Indicated At Specific Points
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 Replies!   View Related
Find Last Instance Of A Word And Insert Blank Row
I would like to be able to add vb code to my developing macro that searches within 1 column for the last instance of a particular word, then inserts a blank row under that word. All the data is sorted, so the words will be used multiple times, but I need the row to go under the last instance of each word in the column.


View Replies!   View Related
Insert A Blank Column Next To Heading Columns
I am doing a lookup, populating a column (which works although slowly) then (in this part) doing a search on the header row.. If the heading contains the word "Category", I wish to insert a blank column next to (the column containing the heading), colour it yellow then look for the next heading and repeat if found.

It appears to clear my first row so I must have my columns referred incorrectly as rows

Dim FWord As String
Dim i As Integer
Dim lCol As Long
Dim MyString As String
.
.
FWord = "Category"
lCol = Range("A1").End(xlToRight).Column
For i = 1 To lCol
Cells(1, i).Value = MyString '

View Replies!   View Related
Macro Insert Cells In First Blank Column
I am trying to come up with a code that will copy column A entirely, and insert the copied cells into the first blank column. In the columns with information in them, there will always be text in the first cell, so that can be used as the test to find the first blank column, but I'm not sure how to get this done for my macro.

View Replies!   View Related
Insert Values And Skip Blank Rows
I want my macro to consider the # of rows of data on a spreadsheet and insert values into columns for those rows but then stop when it hits a blank one. For simplified example below, I have 3 columns in my spreadsheet. Row 1 has headers of Location, Status, and Effective Date. The first 5 rows of data under the headers have a value in the Location column (San Jose, Oakland, etc). When I run the macro, I want it to insert a value of "ACTIVE" in the Status column for each row that has a value in the Location column. Also, a single Effective Date value is stored in a cell elsewere in the spreadsheet, and I want the macro to insert that value in the Effective Date column for each row that has a value in the Location column. When it hits a blank row - row 7 in this spreadsheet - I want it to stop.

Location| Status | Effective Date
San Jose
Oakland
Austin
Houston
Phoenix

I currently am using an IF statement in the Status column fields to say if Location = blank, then blank, else "ACTIVE". That works, except that the # of rows populated in Location column could be 5 or 1,000, and inserting the formula that many times seems to quickly increase the file size.

View Replies!   View Related
Insert Blank Rows Where Date In Column D Changes
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 Replies!   View Related
Insert Blank Column And Copy/paste Values
I'd like to have done is to have a blank column inserted between columns W and X(these values change so the VBA statement should reference the end of the columns) and the values that are now in column Y(April 17th values) pasted as values into the now empty column X. I would like to do this for tabs Ann-Sheet 2. I'm having a bit of trouble with setting up the loop that would go through the desired sheets.

View Replies!   View Related
Link Cells From One File To Another And Insert A New Line On The File
when I link cells from one file to another and insert a new line on the file I am linking to. the file that is linking goes down as follows:

=Safety.xls!$A$5
=Safety.xls!$A$6

How do I get Cell A5, to link to Cell A5 even if I add a new line in the Safety.xls spreadsheet. Taking the $ away does not work I have tried that.

View Replies!   View Related
Insert Single Blank Row Between Multi-sheet Paste Process
I have mananged to find and tweak the following code in order to serve my copy / paste to master sheet requirements.

At the bottom of the code I have added a delete rows based on column contents routine although I'm not sure if it is actually the right one to use.

I have included it in the same module in order to tidy up the whole process and have it all operate under one click.

As stated on the sheet in this exmple, The paste could consist of any number of sheets although the range is always the same on each sheet. (only amounting to around 20 rows that we are dealing with so no reams of data with odd empty rows that would take an age to find otherwise).

The data can look like it does on Sheet 2 here and also could look like it does on sheet 3. (this data is coming from a sales rep's order sheet)

From the routine in module 1, I just don't know where an "add an empty row" or delete all empty rows bar 1" code would need to be inserted in order to keep the process going until all sheets are copied.

I'm aware that with the delete code at the bottom, the whole copy process is completing before the delete process then does it's bit so think I'm on the right track in thinking that the delete all empty rows but leave one" requirements needs to be further up the code but I just don't how to get the result I need or where that code should break into the routine.


The only other way I can think to acheive the result is to allow the range to increase by 1 row on the form but somehow make sure that the last row in the range contains a character in column B to "trick/force" the delete routine to leave that row in. That would be do-able but the trouble is, how do you get a value into a cell that the delete proces would treat as data but not be visible so keeping my spacer line tidy? I've tried just putting a space in the last row of the range in column B but the row still gets deleted.

View Replies!   View Related
Insert Blank Rows At The End Of The Each Data Segment To Add Totals For Each Individual
I need to insert blank rows at the end of the each data segment to add totals for each individual. Is there a macro that I can use for this? I'm attaching an example file.

View Replies!   View Related
Replace #VALUE! Error For Blank Cells With Blank Cell
The formula below calculates appropriately, however, if any of the cells (E12,E14, E21, E22, E28, E29) are blank, it returns a #VALUE! error. I would like the cell to remain blank. How can I do this? The formula is listed below.

=(SUM(E34-E6)-(SUM((E12)+E14)-(SUM((E21)+E22)-(SUM(E28)+E29))))


View Replies!   View Related
Copy Or Create 'blank Cell' As 'blank'
using a formula to copy a cell A1. if A1 is blank, i need forumula result in blank instead 0...is it possible..

View Replies!   View Related
First Blank Cell In Column & Offset To 1st Blank In Row
I have checked archives, some similiar but not quite what I want.

In code below I want the output instead of going offset one column to right in same row to go one column to right into the next blank cell.

View Replies!   View Related
Convert Macro In 1 Line To Multiple Line Code
i hv following code

(i use generate macro)

my question is how to arrange the code from one line to multiple like :-

following code show in excel macro environment is one striaght line.


' Create new var on yr , and replace 2006 to CY06.

ActiveCell.FormulaR1C1 = _

View Replies!   View Related
Report A List Line By Line Based On Name Criteria
Currently I am using Excel 2000. I did a search and couldn't seem to find the answer I was looking for. Hopefully someone can help. It would be greatly appreciated since I have been trying to figure out how to do this for days.

I am trying to create a worksheet that will pull info from a row on one sheet to another sheet based on the name; then continue to list the info on each line afterward until there is no more of the that particular criteria. See below (this is just an example to simplify the data but would need the same process):

This would be the data on sheet 1:

Name Bonus Commision

Tom $45 $50
Mary $25 $75
Mary $30 $80
Tom $60 $50
Tom $90 $25

What I would like to do is have 2 more sheets. One would be Tom's sheet and the other would be Mary's sheet and it would look like this:

Tom's Sheet:

Tom $45 $50
Tom $60 $50
Tom $90 $25

Mary's Sheet:

Mary $25 $75
Mary $30 $80

I am not completely familiar with all of the functions in Excel and compared to you guys I am a complete novice.

Is this possible to do in Excel without using a macro?

View Replies!   View Related
Put All Values Of A Specifice Line In That Matrix In A Line
I'm working with matrix in vba and i'm trying to put all values of a specific line in that matrix in a line in excel. For example:

Matrix =
1 2
3 4

and i want Range("B1:B2") = 3 | 4 (the second line of that matrix).
Is there any way of doing this without perfoming a loop?
[something like Range("B1:B2").value = 'range'(matrix(0,0) , matrix(0,1)) ]


View Replies!   View Related
Line Graph Not To Continue The Line If There Is Nothing In Row 10 Of The Table
With the attached spreadsheet i cannot workout how with the line graph not to continue the line if there is nothing in row 10 of the table.

for eaxmple if there is no value in cell M10, i would like the actual addtion line to stop at the last value on the graph that was in L10.

View Replies!   View Related
Line Connecting A Cell To A Table....and Locked On The Cell
I have multiple data tables in one sheet...and each one has a chart...in that sheet. To better allow the viewer to coordinate quickly which data table corresponds to which cart...i want a line between them. Excel already has the draw line function, but if you move the chart around, the line doesn't move.

View Replies!   View Related
VB- Line Of Numbers In One Cell
I have a line of numbers in one cell (B2). They look like this;

1, 21, 21, 94, 64, 2, 213

These numbers are currently in Decimal format (more on that later).

I need to "split" these numbers into individual cells and drop the comma. It would look like this;

B3 B4 B5 B6 B7 B8 B9
1 21 21 94 64 2 213

I know I can use the "text to column" function but I was hoping for a VB script to do this.

The second part is I then need to convert the numbers from decimal to Hex and add a "0" to the front of any single digit numbers.

I can do this part using standard excel formulas with no problems but if it could be added to the script then that would be great.

The final thing needs to look like this;

B3 B4 B5 B6 B7 B8 B9
01 15 15 5E 40 02 D5

View Replies!   View Related
Make A Cell Blank If Another Cell Is Blank?
Make a cell blank if another cell is blank? This is my formula, it checks two dates. It is in cell M5.

View Replies!   View Related
Merge Row Into One Cell With Line Break
I have a row of data. I want to merge it all into the first cell and have a line break between each line (from each of the cells).

View Replies!   View Related
Conditional Formatting Only Top Line Of Cell
I have a worksheet with conditional formatting applied. My formula works to change the cell color, font color, and strike-through the font if a "yes" answer is indicated. It all works well EXCEPT that the font color and strike-through ONLY apply to the top line on my cell.

Each cell has a title line, and a few paragraphs in it, with separations between the title line and the paragraphs. I kind of like having the title line a different font, but when it's time to strikethrough I want ALL of the text struck through.

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved