Insert new row as cell contents change
Insert new row as cell contents change. After importing
data I have a spread sheet with a column that contains a
series of alpha numeric characters. At various random
intervals in this column the contents change. EG rows 1 to
4 could contain ABC, then rows 5 to 15 could become 222. I
am looking for a method to insert a blank row
automatically between the rows were the contents change.
If you are familiar with VBA the code below will do what you want.
Preselect the column of data first
Dim myCell As Range
Dim sCurrVal As String
...Clear cell value without macro?
Is there any way to clear a cell value when the sheet is selected or
the workbook opened without using a macro?
Click on the cell and press the <delete> key.
Is this what you mean?
On Dec 9, 1:44=A0pm, Ed from AZ <prof_ofw...@yahoo.com> wrote:
> Is there any way to clear a cell value when the sheet is selected or
> the workbook opened without using a macro?
> On Dec 9, 1:44 pm, Ed from AZ wrote:
> > Is there any way to clear a cell value when the sheet is selected or
> > the workbook opened without using a macro?
On Dec 9, 7:02=A0a...VBA to add and remove text within cells
Hi, I have a field named "Postal" at the top of column F that always include
a number with 5 digits then a city name then a region name, such as "11090
CARCASSONNE Linguadoca-Rossiglione". I need to create a program to have this
field changed as following : "F-11090", then copying "CARCASSONNE" into the
City field which is empty (column G). The city name is always starting just
one space character after the postcode, same thing for the region name, it
always starts one space character after the city name. The region has to be
...Reading ranges and copying data from Excel when it is not open
Is there a way in code to copy data out of an Excel file even if Excel is
not up and running? If Excel were open, I'd copy, say, the first 30 rows
and paste the info to PowerPoint. Then, since rows 1-5 are for column
headings, I'd hide rows 6-30 and copy a new range which would look involve
30 rows, but since rows 6-30 were hidden would be 1-5 and then 31-55 as a
I am trying to do this in VBA without opening Excel and instatiating
objects, etc. Is it possible? What VBA commands would I use?
You would need to treat the excel file as a database and use ADO to g...How do I maintain formatting?
I'm using Word 2K and when I save as a .txt file all formatting is lost. I
use a Text to HTML converter prior to uploading to a website. How do I go
about preserving the formatting so that the web doc looks like the one I
created in Word?
Txt is plain text and that does not support formatting.
Html does support formatting, but its requirements are entirely different
from those of a Word document and so there is little correlation between the
two. You can view what the Word document will look like in html by using the
<>&...empty items in deleted items folder
Multiple items appear in Outlook Deleted items folder, no sender, receiver,
or message. Some happen when I am not on my computer. Changed passwords,
checked virus scan (multiple). Tried Microsoft reinstall fix, problem still
What version of Outlook are you using? What anti-virus software are you
using? Are you using the Hotmail connector?
Ben M. Schorr, MVP
Roland Schorr & Tower
Author: The Lawyer's Guide to Microsoft Outlook 2007:
"Ra...How do I set text to top of cell next to wrap text in Excel?
In the cell next to a wrap text cell, I want the text to appear at the top of
the cell, not at the bottom. Ex. cell A1 is a title for that line and cell
A2 is a specification for the title. The specification is much longer than
the title so I use wrap text, but when the row height is elongated to show
all the text, the title ends up even with the bottom of the spec. How can I
make the title even with the top of the cell?
Format-->Cell, Alignment tab. Set vertical alignment to top.
"Carpenter Gary" <Carpenter Gary@discussio...Help with data points in line graph in Chart function in Excel.
I added data to an existing line graph and the data points do not align
properly with the values on the Y axis. For example, a data point valued at
119 lines up between 400 and 800 on the Y axis. I know there's a way to fix
this, but just can't find it. Thanks!
...Can't install CDO (Collaboration Data Objects)
Trying to install CDO under Outlook/Office 2003. I go to
Advanced Customization under the Office setup, find
Outlook, and change Collaboration Data Objects to Run
from my Computer, then click Update.
It starts to work, then pops up the message:
Error 1311. Source file not found: X:\L9561403.CAB.
Verify that the file exists and that you can access it.
The CD does have the file on there (marked hidden). I
copied all of the install files to a local directory and
unmarked them hidden just to try, but no change.
This is driving me crazy - what could possibly be wrong???
Never mind, ju...How do I fix a formula in a cell?
I'm using Excel 2000. I have 2 sheets in my work book. Cell A1 in Sheet
2 has a simple formula: "=Sheet1!A1". Thus the data in sheet 1 A1 is
always replicated in Sheet 2 A1. However, if I "cut" Sheet 1 A1 and
"paste" somewhere else in the book, Sheet 2 A1 still reads the
original data in Sheet 1 A1 and the formula in Sheet 2 A1 has changed
to refer to the new location of the pasted data.
Normally, this is not a problem but in this case I want the data in
Sheet 2 A1 to read whatever is in Sheet 1 A1 at all times. I do not
want the formula "=Sheet1!A...Formula to determine Cell Color Format (Shading)
I was wondering if there is any formula or macro to use
to evaluate cells that contain a certain format.
I want to create a Yes/No column for when cells are
formatted with a color so that I can sort a large amount
of data based on the color the cell is shaded.
I don't want to do find>all by format that is available
on the Finder menu because I cannot populate the other
column based on that and I want to be able to frequently
evaluate only rows of data contain the cells colored a
you can use a macro that will analyze the color like:
if sheets(&quo...Setting a reference to a libray in code
I am trying to reset references to Excel and Word from Access 2007 i.e. code
developed on 2007 with office 2007 but when dstributed to users the reference
is broken because they are on say office 2000.
I have read the link provided in previous posts on this subject but it
doesnt seem to be of any help to me.
What i do is look through the references to see if any are broken (i already
know the excel one is though by looking at tools > references) so
For each ref in References
if ref.isbroken then
'fix the reference
I was then going to fix the br...User Data
Is it possible to access the user table in MSCRM?
I want to capture some more details for given users say his/her competency
level. I am not able to add a field via Deployment Manager.
Can we do it using a custom build application? Competency has to be stored
for each user. So I would require having competency level corresponding to
Modifying the fields for userinformation isn't possible in this version. In
mscrm 3.0 it will be possible.
You will have to wait untill the crm 3.0 release or built your own
application. You can use the systemuserid for making the ma...line spacing within a cell
When using "center across selection" with Wrap Text, how
do you get a full line of space between information you
are placing in the same cell that I have expanded
I'm not sure what you want, but if you're looking to place a hard return
after a line of text to get that blank space, use Alt+Enter.
Michael J. Malinsky
"Kristi" <email@example.com> wrote in message
> When using "center across selection" with Wrap Text, how
> do you get a full line of spac...copy data vba
I need a macro that searches the client name in b2 in other workbook
that has a sheet for each client. So the name=B4s cliente in B2 should
match matches with the client=B4s name sheet and copy the data of row a2
to e2. Is this possible?
CN = Range("B2").Value
MS Excel MVP
"1234" <firstname.lastname@example.org> wrote in message
I...count cells in a document, but excude cells with a "0"
I want to count the number of cells that have a value greater than "0", but
not use a range. The cells that will be counted are every other cell in that
so my answer would be "3", the amount of cells that have a value greater
than "0". the arrows are not on the work sheet they are just used to point
out which cell I need counted.
You have not given any cell references where your data is but a fomual like
this should be close...
=SUMPRODUCT(--(MOD(ROW($A$1:$A$10), 2) = 0), --($...Maintaining Links
All, I have a db that is linked to a table of another db. They are kept in
one folder. Whenver I move the folder to another comp or change the location
I need to re-link. How do I maintain the link so that I don't have to relink
every time I move the folder? Thanks in advance.
What you are asking is "How can I be in 2 places at the same time?" Do I
really need to answer that? On the other hand, you can have the front-end
app automatically ask for the new location. Have a look at the following:
Arvin Meyer, MCP, MVP
http://...Cell protection in Excel
I use Microsoft Excel in the latest two versions of
Microsoft Office to test pupils at school in mathematics
and English language.
This involves using hidden formulas and locked cells with
answers in - all protected by a password. It doesn't work!
This is even the case when access is denied to the
The youngsters just insert the coordinates of the answer
cells into the 'Name Box' (top left), go to the cell and
copy the answer out into the answer cell.
Am I doing something wrong?
Is there any way of preventing access by this method or
copyin...how to keep data mods in a doomed transaction
If i have a doomed (or will be doomed) transaction and i want to write the error and context (like proc name and
parameters) to a table, how can i get that write to survive the ensuing ROLLBACK?
I solved this but wonder if there is another way. I couldn't figure an all TSQL solution. Is there one? Here is what
I created a SQLCLR proc that accepts the parameters necessary. It first connects to the Context Connection. Once there
builds a connection string based on that information:
"SELECT 'Data Source=' + @@servername + ';Initial Catalog=' + d...How to check if each value in a cell range is contained in a second cell range?
For example, suppose I have a range
and a second range
Is there a formula that would return a value of, say, 1 for each valu
in the first range that is NOT contained in the second range.
For example, here, I would want to return
Thanks very much,
ModelerGirl's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=636
View this thread: http://www.excelforum.com/showthread.php?threadid=50664
On Mon, 30 Jan 2006 22:06:04 -0600, ModelerGirl
<Mo...Calculating page/report sums using calculated data from subquery
Here is a scenario from my report:
The report will grab data with a main query into a parent row
Depending on the data, a child row may exist, if it does the report will
The child row will use data from the parent row in order to do a calculation
in the child
All of this works just fine and the report data is correct.
Now, what I am having issues with is doing the page/report sum calculations.
The requirements dictate that I need to create a sum of both the parent
column and also the child column in the totals.
I have been trying to do this all day and cannot see...how to manually copy text from one cell to end of other text cell
probably stupid question, but anyways...
I want to manually copy the content of 1 text cell to the end of another
text cell, on the same sheet.
For that, I copy the content of the source cell (ctrl-C), I move the cursor
to the target cell and I hit "F2" to position the cursor at the end of the
text of the target cell.
But as soon as I hit "F2" the text of the source cell is "unselected" and
hence cannot copy the source text...
help much appreciated!
do this when you select the cell to copy don't press Ctrl C, you will see
the text in t...View Excel Cell Location
Operating System: Mac OS X 10.6 (Snow Leopard)
I'm new to the Mac, previously used PC. When I open Excel, the row at the top are numbers instead of letters. Can't find anything in preferences, it's making it hard for me to understand the calculations. Thanks.
It sounds like you were on the right track but gave up a bit too soon ;-)
Excel> Preferences> General, remove the check on "Use R1C1 reference style".
On 2/10/10 7:13 PM, in article 59bb29eb.-1@webcrossing.JaKIaxP2ac0,
"Peggy_F@of...Cells will not calculate
I am using the replace feature to modify a rather long formula. I have to
modify it in each column, abour 30 cells per column.
Anyway I am replacing $D with $E = the replace feature works perfectly but
after the change the cell does not calculate and return the data... HELP. It
is far to tedious to make the chabge manually.
Even with calculation set to manual, the cell should recalculate after
making a find and replace change. Your form...Replace a spreadsheets named cells/ranges with exact cell address.
I have a named range (A1:A6) which is called MyRange. Lets say each cell is
populated with a number A1 = 1, A2 = 2. A3 = 3 etc. In the row (B1:B6)
beneath it, I have entered =MyRange in each cell. As such the values in
B1:B6 mirror those in A1:A6. That is, even though I use MyRange in each cell
in Row B, Excel knows which cell to pull from in rowA to correctly populate
the cells in Row B.
Now If I use the Macro below to delete the named ranges I get the following
range in each of the RowB cells =A1:A6. Once again Excel knows which cell to
pull from in Row A to populate cells in Row...