add cell

i have a long list all in one column.   the data in the first fifteen (
thrue 15) is related and must be kept in sequence. the first fiftee
cells repeats with changes in its data for the next fifteen (16  thru
30) and also must be kept in sequence. this repeats over and over for 
list of about 4000 cells.  
what i need to do is this:  i need to insert  an empt
cell betwwn each group of fifteen cells.-to seperate them.   between
15 & 16, 30 & 31  etc without disturbing the continuity of each 15 cel
grouping..

thankyou so much

--
ROL
-----------------------------------------------------------------------
ROLG's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1606
View this thread: http://www.excelforum.com/showthread.php?threadid=32055

0
11/26/2004 12:18:11 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
271 Views

Similar Articles

[PageSpeed] 48

This worked ok for me:

Option Explicit
Sub testme()
    Dim iCtr As Long
    Dim LastRow As Long
    Dim myRng As Range
    With ActiveSheet
        LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
        Set myRng = Nothing
        For iCtr = 16 To LastRow Step 15
            If myRng Is Nothing Then
                Set myRng = .Cells(iCtr, "A")
            Else
                Set myRng = Union(.Cells(iCtr, "A"), myRng)
            End If
        Next iCtr
    End With
    
    If myRng Is Nothing Then
        'do nothing
    Else
        myRng.EntireRow.Insert
    End If
End Sub



ROLG wrote:
> 
> i have a long list all in one column.   the data in the first fifteen (1
> thrue 15) is related and must be kept in sequence. the first fifteen
> cells repeats with changes in its data for the next fifteen (16  thrue
> 30) and also must be kept in sequence. this repeats over and over for a
> list of about 4000 cells.
> what i need to do is this:  i need to insert  an empty
> cell betwwn each group of fifteen cells.-to seperate them.   between
> 15 & 16, 30 & 31  etc without disturbing the continuity of each 15 cell
> grouping..
> 
> thankyou so much.
> 
> --
> ROLG
> ------------------------------------------------------------------------
> ROLG's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16066
> View this thread: http://www.excelforum.com/showthread.php?threadid=320559

-- 

Dave Peterson
0
ec357201 (5290)
11/26/2004 3:09:58 PM
thankyou for your very appreciated  help

--
ROL
-----------------------------------------------------------------------
ROLG's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1606
View this thread: http://www.excelforum.com/showthread.php?threadid=32055

0
11/26/2004 6:34:19 PM
Reply:

Similar Artilces:

How to add a search box to Outlook as a Toolbar??
Hi, We have a search box on our web site. We wanted to integrate this search box into outlook so a user could simply search our site from within outlook. This search should query the database on our side I would assume...perhaps pass the search criteria in the url?? how can this be done? Thx.. -WestSide Anyone know? ...

In macros, an instruction that returns the position of the Active Cell
In a macro, how can I make it return the position of the Active Cell Colum & Line. Or how can I set that position as a Condition; e.g. �If ActiveCell i B7 Then�.� Or �If ActiveCell belongs to Column G Then�.� Thanks in advance for any help. AC -- ac ----------------------------------------------------------------------- aca's Profile: http://www.msusenet.com/member.php?userid=385 View this thread: http://www.msusenet.com/t-187368645 Hi Sub test() Dim ws1 As Worksheet Set ws1 = Workbooks("Tester.xls").Sheets("Sheet2") With ws1 MsgBox ("Cell address is &qu...

a cell contains both text and number
how do i make a calculation if a cell has text and a number. Example the cell says "3 weeks" I want to calculate 3 weeks to read in the next column as "120 hours" =LEFT(A1,1)*1*40&" hours" Vaya con Dios, Chuck, CABGx3 "text and formulas" <text and formulas@discussions.microsoft.com> wrote in message news:D8C17645-DAFF-455A-B144-5B936B97ADCE@microsoft.com... > how do i make a calculation if a cell has text and a number. Example the cell > says "3 weeks" I want to calculate 3 weeks to read in the next column as "120 &g...

How Can I Make The Cursor go to the last letter of the cell?
I have a paragraph in the cell and I want to know how to make the cursor go to the end of the paragraph without clicking on it or using sendkey {f2}{end}{right} Thanks! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ anyone? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ I do not think this is possible without substantial coding. Although i...

VB- If first cell with formula is blank, all cells in column returns blank.
Hi all, I am using ADO to connect to an excel sheet and display the data in vb form. In excel there is a column named "TAT" which has a formula t add two othe cell values to it. If any of the two cells is blank the the TAT col remains blank. If the very first cell in the TAT column has some value then VB showa all cell values in that column. But if the first cell is empty then al cells in tat column are shown blank, even if there are values in othe cells. What's the solution for this? Thank -- Message posted from http://www.ExcelForum.com Hi Of course you can try with some...

Cell Values
I would like to return the value of another cell(C1) if a cell has a value in it, otherwise I want it to return a value of 0. Example: IF(A1="any value",C1,0) This formula would be written in B1. "Any value" would be numbers or text. Thanks 4 your help!!! Try: =IF(A1<>"",C1,0) or =IF(ISBLANK(A1),0,C1) Good Luck, Mark Graesser "GaryW" <gary.wicker@acadiapolymers.com> wrote in message news:040b01c34721$41fe8d00$a301280a@phx.gbl... > I would like to return the value of another cell(C1) if a > cell has a value in it, otherwise I...

Having Formatting Remain on "Called" cell using IF function
I'm using an IF function to call some other cells, but I want to have the various words color coded, eg ("Certified" is green, "Recertify" is yellow, and "Not Certified" is red). I have the IF function working correctly, but when it "calls" the cell to have it display the appropriate word, the color of each word is not brought along with it...only the text. Is there a way to be able to color code it so that the appropriate color appears? Thanks! -- Zaraf ------------------------------------------------------------------------ Zaraf's Profi...

How do I use auto fill to add information from an existing source?
I am trying to figure out how to use some type of auto fill. What I have to do is put in a customer name and then I need to have the address, city, state, zip and phone number automatically fill in. How would I do that? Assume Sheet2's cols A to F is the existing reference, where col A contains the names, cols B to F contain the other info, eg: address, city, state, zip and phone number In Sheet1, you have the names listed in A2 down Put in B2: =IF($A2="","",VLOOKUP($A2,Sheet2!$A:$F,COLUMNS($A:A)+1,0)) Copy B2 across to F2, fill down as far as required -- Max ...

Some cells do not recognize email address
Some of the ceels in my worksheet recognize email addresses so I can click on them and send an email. Other cells do not. the format is right. How do a get a cell to recognize an email address? Not knowing exactly what you have in those cells, expect they are complete email addresses, what version of Excel, and your options would suggest the following: If you have Excel 2002 (Excel XL) or higher: you can suppress hyperlink generation under the Auto Correct from the Tools menu. Tools (menu), Autocorrect Options, Autoformat as you type (Tab). so you want to do the opposite. Next...

Calculate to another cell as default value
Hi. Given the following: A B C 1 5 9 __ 2 8 10 __ I'd like for the user to enter values in A and B and have Excel display B minus A in C as a DEFAULT value. That is, for row 1, once the user enters 9 in B, I want to display 4 in C but give the user the option to override the calculation. A formula in C won't work because the user would delete the formula if he overrides it. Thanx. I don't Why not use an extra column (C) and have this formula in D: =IF(C1="",B1-A1,C1) -- Kind regards, Niek Otten Microsoft MVP - Excel "...

Add new resource / RBS
Hi, I have a client requirement to add new resource using PWA only. With the following requirement, I'm not sure if I should add new resources under the RBS look up table or just add them using the Manage Users/Groups option. Level 1 - Department Level 2 - Group (with security permission) Level 3 - Users/Resources. The requirement is that resources under one department can only view projects under that department, except for those in the Admin group can view projects across departments. Also another requirement is that if I select a department (level 1) a...

Last cell in new formula
Can anyone please help? The code: Sub test() Dim x As Range Worksheets("sheet1").Activate Set x = Cells(Rows.Count, "N").End(xlUp) MsgBox x.Address End Sub I would like to find the last cell in column N and instead of the result being displayed in A msgbox, I would like to use the cell address in a formula after the code above e.g.: range("b2").formula = _ "sumproduct((N8:LASTCELLADDRESS>=0)*(N8:LASTCELLADDRESS<=1000)) Hi, Try this Dim LASTCELLADDRESS As Long LASTCELLADDRESS = Cells(Cells.Rows.Count, "...

concatenate cells from a column and paste to a cell
I'm trying to write a macro that will loop through a column and copy the data to a single concatenated string which will be pasted to another cell. The macro should step through the column of data until it reaches the end of the data set or an empty cell. I could name a range if needed. Any help getting me started???? The concatenate( ) function won't work with ranges..., so I assume I'll have to write a macro to loop. This will do it, but you may not be happy with the results since the resulting text string may be longer than Excel allows. This code runs from the cel...

Splitting Data in a cell, left or right of a separator.
I need to split some data in a cell to the right of a separator, which is a colon. eg: "CONSUMABLE:HPC92298X" Would someone be able to point me in the right direction.. many thanks, Ross One way is to use Data > Text to Columns Select the column Click Data > Text to columns (Delimited) Click Next In step 2: Type a colon in the "Other:" box Click Finish -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "ross" <ross_anderson57@hotmail.com> wrote in message news:01AD4975-1539-411E-B5D8-04B569268783@microsoft.c...

In Excel is it possible to hide a row condtional upon a cell val..
In Excel is it possible to hide a row condtional upon a cell value? with a macro yes,or you could format white text and all values would disapear. -- paul remove nospam for email addy! "Louise" wrote: > In Excel is it possible to hide a row condtional upon a cell value? The white text makes the text disappear, but not the entire row or column of cells. With a macro, is it possible to make the cells automatically unhide then if the data becomes relevant again due to changes? "paul" wrote: > with a macro yes,or you could format white text and all values woul...

formula will not add up right
I put in =sum(E1+G23) & the total in the cell where I put the formula comes up(2 or 3) cents off? It comes up short when I put =sum(E1*G23). Not al the time but off & on. I have to add stuff on paper to check that the total is right. I should not have to take all this time to use Excel. Your question has already been answered an hour and a half ago -- Kind regards, Niek Otten "Kickstart" <Kickstart@discussions.microsoft.com> wrote in message news:DDD6AB31-657B-4F64-8709-D9E36CF96C41@microsoft.com... >I put in =sum(E1+G23) & the total in the cell where I p...

Can not Remove Locitech stuff in XP Add/Remove
I have two entries in XP Add/Remove, "Logitech Desktop Messenger" and "Logitech Camera Driver". I have tried all the tricks that I can find including going into the Registry, but nothing will get rid of them. I would appreciate any tips that you may have to offer. ...

How do I add the file last modified date into an Excel header?
It is easy to show the date a document was last modified in MS-Word by inserting the field: savedate into the footer. A very common requirement. But there does not appear to be any way, or at least no easy way to do this in Excel. I CAN'T BELIEVE IT! They still haven't added this. Excel forces you to put in the current date. YUK! Why would I want that in my print out? I want to know when it was last modified, not when I printed it. ARG!!! From an earlier posting Hi use the following UDF: Function DocProps(prop As String) application.volatile On Error GoTo err_value ...

What is Micosoft Office Live Add-in Sign-In?
Everytime I close a Office program, a message comes up this Addin stopped and must be closed. What is this and how do I turn it off? "Ringer" <Ringer@discussions.microsoft.com> wrote in message news:A42C0902-D783-4803-99B3-D125541D8F4F@microsoft.com... > Everytime I close a Office program, a message comes up this Addin stopped > and > must be closed. What is this and how do I turn it off? Add/Remove programs and uninstall it. You can head to Microsoft's site to see what it is. -- "Don't pick a fight with an old man. If he is too old...

How do I add error bars unique to each data point?
I have a data series that I want to add error bars to, however I would like to have unique error bar values for each individual point? Is this possible? Thank you.. Glenn Hi, On the error bar tab of the format dialog for a data series are 2 ref edit boxes that will allow you to select a range of cells containing a unique value for each point. Cheers Andy Glenn wrote: > I have a data series that I want to add error bars to, however I would like > to have unique error bar values for each individual point? Is this possible? > Thank you.. > Glenn -- Andy Pope, Microsoft MVP ...

Adjacent cell belong to same merged cell?
How do I compare two adjacent cells to determine if they belong to the same merged cell? Adjacent cells cannot be merged cells. If two or more cells are merged, they become one cell. That is what "merge" means. Perhaps there is another way to phrase the question? Gord Dibben Excel MVP On Fri, 25 Mar 2005 11:11:02 -0800, DrKilbert <DrKilbert@discussions.microsoft.com> wrote: >How do I compare two adjacent cells to determine if they belong to the same >merged cell? Thanks Gord for getting back to me on this. Let me explain in more detail. I have a worksheet t...

Cell Format #7
Hello, I have a column where I would like to format as ##-##-##- ##-##-##. I went to Format > Cells and format it in Custom category. It works fine when I enter values, but when the data starts with a text, it won't work. Any suggestions. When I entered 123456789123, it converts to 12-34-56-78- 91-23. When I entered AU1234567890, it doesn't do anything. I want it to covert to AU-12-34-56-78-90. Any ideas would be helpful. You can only format numbers that way, you could custom format as "AU"-##-##-##-##-## and just enter the numbers 1234567890 -- Regards, ...

Sms add-inn
I am using the outlook Sms add-in but whenever I attempt to send a message, I recieve the message that the phone number passed in is invalid. I get my "TO:" data from my contacts folder. Where do I change the phone number format, and what is the correct format. If anyone knows how to resolve this problem your help will be greatly appreciated. BTW I connect my phone using bluetooth. ...

date and time in 2 cells to create subtraction in 3rd
I have 2 cells that are in the format line of A1: 27/06/2006 9:42:00 pm B1: 7/06/2006 9:52:05 pm Want third column C1: 00:10 I wish to subtract the two cells to get the difference in time i.e 10:00 (only interested in nearest minute) Then a average at the end of the column of the the times in this 3rd column as a result of the difference between the 2 times. How would I do this? I could do it if it was just numbers but once it is in this time/dtae format I am completely lost! Thanks MIke Hi Mike, =MOD(B1,1)-MOD(A1,1) Format as Time You can use the AVERAGE function on the ...

Merging text strings into a single cell
HELP!!!! I have created 2 separate columns of text data in a large spread sheet (over 600 rows of data). Now I need to merge the 2 columns together so that the text descriptions from each column are combined together into a single column. If the cells were numeric it would not be a problem (i.e. if C3 were the finished result I was looking for cell C3 would read "=A3+B3. Only problem is Excel simply tells me the formula is invalid because it's text data. Merging the cells of each row simply cuts the text off from the 2nd cell. How can this problem be resolved????? Hi you'...