Free Microsoft Excel 2013 Quick Reference

Excel 2003 cell limit

(i'm using Visual Studio 2005 and Excel 2003 -- cross posted in vc.mfcole
group)

i have read that Excel 2003's char limit for a cell is 32,767 chars, of
which only 1024 are displayable in the cell (the remainder are visible in the
expanded command line (or whatever it's called...at the top of the
worksheet)). that's fine.

however, i have text that is 1405 chars and Range.put_Value(str_1405) fails
unless i set str_1405 = left(str_1405, 910). the actual max value is
somewhere between 910 and 925.

since i'm well below the 32,767 limit i feel this should work. has anyone
else run into this? suggestions?? i've tried Range.NoteText(vStr, vStart,
vEnd) and it always fails w/ "type mismatch). i've tried
Range.AddComment(vStr) - it fails like NoteText, although i'm not sure that a
Comment is the same as a Note.

tia


I have a chronological log in Excel 2003 (Windows XP) which shows to on-going status of various projects. As time goes by I add comments to some of the cells and the cells get fairly large.
I have some cells (with word wrap) which now have approximately 1550 characters in them. After this many characters, word wrap doesn't work for any additional characters in that cell, so the rest of the entry doesn't display on the worksheet.
Question: Is there a size limit on the number of characters a cell will display with word wrap? Is there a workaround to this problem, other than inserting another row beneath the questionable one, to continue the entry ?

I have been on microsoft.public.excel.crashesgpfs but so far just the
one reply (from someone who has exactly the same problem as we do and
has not yet solved it either).

Anyone here can help???

My boss has this massive Excel spreadsheet with a number of worksheets
linked with other spreadsheets etc.

He is using Excel 2000 on Office 2000.

His computer was crashing with the above whenever he tried to do work
on it. We bought him a brand new computer, 1 gig of RAM, 80 GB of HDD.
We're a small company - a charity - so this was as powerful as we
could afford.

This was a week ago and it's happening again.

We desperately need to get this spreadsheet working, and he cannot
simplify it (or rather, that would take too long). I have changed the
Virtual Memory settings of the PC and it made no difference.

Having done some googling around it seems that this is because Excel
2000 crashes with large or complex spreadsheets - According to
Knowledge Base report http://support.microsoft.com/?kbid=313275

> In versions of Excel earlier than Microsoft Excel 2002,
> the memory limit is 64 MB. In Excel 2002, the limit is
> increased to 128 MB. In Microsoft Office Excel 2003,
> the limit is increased to 1gigabyte (GB).
> Because this is a per-instance limit, this problem may
> occur if you have two or three large workbooks open, or
> one very large workbook. If you are working with several
> workbooks, try to open them in separate instances of Excel.

We've tried everything they suggest apart from upgrading to Excel
2003.

We're going to buy Excel 2003 this morning and install it onto his
machine into Office 2000 - or we may even go the whole hog and get
Office 2003 as well, we'll see.

We were hoping that this would 100% SOLVE THE ISSUE?????

Microsoft seem to suggest it will, but "upgrade your software" is
pretty much their solution to everything (for example, I still use XP
service pack 1, and whenever I have a small computer problem and ask
the computer to find out why, it always says that upgrading to SP2
will solve the problem, which is rubbish as one of the computers
across the room has SP2 and crashes with the same document!)

But now the bombshell. A guy on the other newsgroup has the same
problem, upgraded and this didn't help!! He writes, and I quote:

>I don't think upgrading will solve your problem. I'm facing the same problem
>with some linked spreadsheets. In the office we work with Excel 2002. I've
>tried it at home where I have Excel 2003 and a pc with 1024 Mb memory. I
>still get the same error.
>I also found the article in the knowledge base and if you read it carefully
>you will see that not only the amount of memory that Excel can handle is
>limited, but also the number of source cells is limited to 32760. As far as I
>can tell (and I am absolutely no pro) is this our problem. You can read that
>Microsoft fixed the amount of memory problem but the article does not mention
>any increase of the number of source cells that Excel can handle.
>For us it's also very important to get the sheets working asap, but I still
>don't know how.

So I guess if anyone has the answer to this question it would help him
and it will help me if the upgrade doesn't work.

Limiting source cells in a huge document? errrm is that a very
timeconsuming complex job? or is there maybe an application that can
help with it?

I guess the first question would be: will upgrading to 2003 increase
the number of source cells (as well as the memory that Excel can
handle).

Perhaps someone has written something in open source that can increase
the number of source cells Excel handles?? I don't know!! There must
be an answer to this obviously common problem!

THANK YOU!!!

TRISTÁN

Is there a way in Excel 2003 to have specific text content of cells set the
color of the background? e.g. all cells containing "RA" have a light green
background, "RB" have light blue.... etc., for a small set of text values.
TIA

Dan
--
Dan E
webbie(removethis)@preferredcountry.com

Hi everyone,

We have an interesting problem that many others seem to have never struck.
When linking a cell from one spreadsheet into a cell into another
spreadsheet, it is hit and miss. Sometimes it will work, sometimes not (using
the same procedure). When it doesn't, I press enter and literally nothing
happens. The spreasheets are both using the exact same version of Excel (2003
SP2). It is rather confusing as cells that are extremely similar are working,
yet other aren't.

Can anyone shed some light on this?

Cheers, littik.

Hi Guys,
Briefly, I run a spreadsheet that pulls in financial data from a provider(Bloomberg) and ranks trades according to parameters I set. It compares each stock to each other; therefore with excel 2003 I could enter a maximum of 255 stocks(255^2=65025). If I programmed any more stocks I would get an "exceed number of rows limit"

On Friday I installed Excel 2007. This has 1m rows allowing me to increase my securities sample set to 1000(1000^2=1m).

However when I run this it completes 9% of the calculations before I get an error message. This states "exceeds maximum number of rows 65k". I then get a VISUAL BASIC "run time error 9, subscript out of range message"

It seems that the programme does not realise that I am now running excel 2007. I have saved the sporeadsheet in excel2007(as a .xlsm) and I open it in Excel 2007. I have no idea why it is still giving me a row limit message...

Any Ideas or input would be greatly appreciated...

Thanks

Excel 2003 limits the number of rows that one can open/work with to 65,536. Is there any way to go beyond this number?

Hi all -

Was wondering if you have discovered any tricks when dealing with Excel 2003's limitations on rows/columns and amount of data points that can be used in a chart (32,000)? Any advice would be great.

When wrapping text in a cell (Excel 2003), does the wrapping only apply to a
limited amount of characters? I notice the wrapping stops after a certain
point. Is there a work around?

Hi,

I am running into a problem specifying a mail merge range in Excel 2002/2003 that only includes non-blank cells. In Excel/Word 2000 I was able to achieve this by hiding unused rows in Excel before performing the mail merge. This also added simplicity to the Excel “form” by showing the user only rows needing to be filled out at the time. In Excel/Word 2002 and beyond, this no longer seems to work, however, one peculiarity seems to make me thing perhaps Excel 2002 and beyond is still capable of limiting a mail merge data source range in this way.

Any ideas of how I might either modify the Excel or Word documents or the VBA code so that only shown/filled rows are included in the mail merge, thus eliminating unnecessary blank pages?

The actual mail merge is being performed using Microsoft Word VBA code (as I ran into far too many problems in executing the mail merge in Excel) while the Excel VBA code is used for showing/hiding cells, clearing the form, determining the number of printed pages in the final step, and saving and continuing the next step in the mail merge process.

Full details to follow and I’d be more than happy to upload screen captures if that would be of help. If you believe this to be a Word VBA solution I will try and find a Word coding forum in which to ask this question.

Thanks much!

-------------------------------------------------------------------------------------

A year ago I came up with an automation “mini-app” that staff can use to generate the correct number of student aide tags needed for the student aides helping them in their classes. This automation uses a batch script, Excel VBA and Word VBA to complete a mail merge in Microsoft Word.

The batch file copies the Excel and Word documents to the user’s Desktop, launches Excel first, inviting staff to fill out the required columns “TEACHER’S LAST NAME,” “STUDENT’S FULL NAME,” “PERIOD # (INCLUDING SUFFIX),” and “TYPE OF TAG.” There is a textbox in column F that allows users to show or hide cells (eight cells are shown by default), a button to clear the form, a button to find out how many printed pages they should expect (based on a mathematical formula that divides the number of shown cells by eight and another formula that divides the number of filled cells by eight) and a button that saves and closes the Excel document.

Upon clicking the “Save, Close and Continue” button in the Excel spreadsheet, the batch script launches the Microsoft Word document on the Desktop where Word VBA code automatically completes the mail merge with the Excel document on the user’s Desktop and initiates the printing of the completed merged document to the default printer, closes the Word mail merge template document and (upon closure of the Word application) deletes the temporary documents off the user’s Desktop.

The version of Microsoft Word used in our building prior to the upcoming school year was Microsoft Office 2000 (the OS is Windows XP). However, we recently upgraded all of our licenses to Microsoft Office 2003 and I soon realized that I also needed to make a few adjustments to my “mini-app” in order for it to work with the upgraded version of Office.

Everything is now working as it should except for one interesting little bug. In column F of the Excel spreadsheet I added a textbox with some VBA code to hide/show rows in the spreadsheet. By default, only nine rows (eight plus the header row in row 1) show as there are eight student aide tags to a printed page once the mail merge has been completed in Word. The instructors can use the textbox to hide or show additional rows as they need to. This works to simplify the user’s view by allowing them to only show the number of rows needed but also, in Excel/Word 2000, creates a situation where (by default) Word only performs a mail merge against the unhidden cells. This limits the number of pages printed and eliminates the unnecessary printing of blank pages following the completed mail merge page(s).

The rub is this. In Excel 2000 hiding unused rows eliminates the blank pages from being generated in Word by limiting the data source range. However, in Excel 2002 and beyond, hiding rows does not limit the data source range in Word and a number of blank pages (based upon the number of rows unhidden in the first place) are generated following the final mail merge. It doesn’t seem to make a difference if I manually rehide the rows or rehide these using the VBA code associated with the textbox.

For example, the Excel document starts with only nine rows unhidden, and if I precede without unhiding any additional rows only one printed page is generated in Microsoft Word. However, if I unhide say the first 50 rows and then, prior to saving and continuing, decide to rehide all but the first nine rows, six additional blank pages are still generated in Microsoft Word upon the completion of the mail merge. Blank pages occur when unhiding more rows than the user fills out.

What I find interesting about this is that there has to be something saved in the Excel 2003 document upon hiding/unhiding rows. I can temporarily rectify the additional blank pages problem by copying the original, untouched, Excel document and reapplying the mail merge. So I’d deduce that there must be something saved in the original, untouched, Excel 2003 document that allowed Word to limit the data source range to only the non-hidden cells; There must be something that changes in the Excel document when I unhide/rehide cells? However, I have not yet been able to duplicate the results.

Any ideas of how I might either modify the Excel or Word documents or the VBA code so that only shown/filled rows are included in the mail merge, thus eliminating unnecessary blank pages?

Thanks much!

-Matt-

----------------------------------------------

Here is the VBA code from the Microsoft Excel file:

Excel - Sheet 1 (Generate Aide Tags App):

Option Explicit
Private varNumCellsToShow
Public varReturnDetected

Private Sub btnHowManyPrintedPgs_Click()
Call HowManyPrintedPgs
End Sub

Private Sub btnNumStudsEnter_Click()
On Error Resume Next
Application.ScreenUpdating = False
varNumCellsToShow = txtNumCellsToShow.Text + 2
If txtNumCellsToShow.Text = 65535 Then
MsgBox ("You entered a number greater than or equal to 65535. Showing all 65535 rows.")
txtNumCellsToShow.Text = 65535
varNumCellsToShow = 65536
Range("B1").Select
End If
If varNumCellsToShow = "" Then
Exit Sub
End If
Cells.Select
Selection.EntireRow.Hidden = False
Rows(varNumCellsToShow & ":65536").Select
Selection.EntireRow.Hidden = True
varNumCellsToShow = ""
End Sub

Private Sub btnSaveAndClose_Click()
ActiveWorkbook.Save
Excel.Application.Quit
End Sub

Private Sub bttnClear_Click()
Application.ScreenUpdating = False
ActiveWindow.SmallScroll Down:=-33
Range("B2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("B2").Select
End Sub

Private Sub txtNumCellsToShow_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
varReturnDetected = 1
Call btnNumStudsEnter_Click
End If
End Sub

Private Sub txtNumCellsToShow_LostFocus()
If varReturnDetected 1 Then
Call btnNumStudsEnter_Click
End If
varReturnDetected = 0
End Sub

Excel - Module 1:

Option Explicit
Private mycount As Long

Sub HowManyPrintedPgs()
Dim FinalRow
Dim LastFilledRow
Dim NumberofPages As Integer
Call NumberUnhiddenBlank
If mycount = 0 Then
FinalRow = (Range("B65536").End(xlUp).Row)
If FinalRow 7 Then
NumberofPages = WorksheetFunction.RoundUp((FinalRow - 1) / 8, 0)
MsgBox ("There are 8 student aide tags to a page and you have elected to print " & FinalRow - 1 & " tags. Tags will print to " & NumberofPages & " pages.")
End If
End If
If mycount > 0 Then
FinalRow = (Range("B65536").End(xlUp).Row) + mycount
LastFilledRow = (Range("B65536").End(xlUp).Row)
If FinalRow 2 Then
NumberofPages = WorksheetFunction.RoundUp((FinalRow - 1) / 8, 0)
MsgBox ("There are 8 tags to a page, you have elected to print " & LastFilledRow - 1 & " tags. IMPORTANT NOTE: You have " & mycount & " blank rows at the bottom of your form. To reduce the number of pages printed, please be sure to adjust your Number of Tags to Create textbox settings. Tags will print to " & NumberofPages & " pages.")
End If
End If
End Sub

Sub NumberUnhiddenBlank()
Dim c As Range
mycount = 0
For Each c In Range("B1:B65536")
If Rows(c.Row).Hidden = False And c.Value = "" Then mycount = mycount + 1
Next c
'MsgBox ("The number of unhidden, blank, rows is " & mycount & ".")
End Sub

Here is the VBA code from the Microsoft Word file:

Option Explicit

Private Sub ActivateMailMerge_Click()
Call Document_Open
End Sub

Private Sub Document_Open()
Dim UserName
UserName = Environ("username")
MsgBox ("This step will take a couple of seconds. Please hang on...")
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:Documents and Settings" & UserName & "DesktopStudent Aides.xls", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="Entire Spreadsheet", SQLStatement _
:="", SQLStatement1:=""
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With

Dim NumPgs As Long

NumPgs = Selection.Information(wdNumberOfPagesInDocument)

If NumPgs < 2 Then
Select Case MsgBox("Please insert " & NumPgs & " page of purple colored paper into the printer and press OK to print or press Cancel to cancel", vbOKCancel, "Printing to the Default Printer...")
Case vbOK
Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
Collate:=True, Background:=True, PrintToFile:=False, PrintZoomColumn:=0, _
PrintZoomRow:=0, PrintZoomPaperWidth:=0, PrintZoomPaperHeight:=0
Case vbCancel
Documents(2).Close SaveChanges:=False
Exit Sub
End Select
End If

If NumPgs >= 2 Then
Select Case MsgBox("Please insert " & NumPgs & " pages of purple colored paper into the printer and press OK to print or press Cancel to cancel", vbOKCancel, "Printing to the Default Printer...")
Case vbOK
Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
Collate:=True, Background:=True, PrintToFile:=False, PrintZoomColumn:=0, _
PrintZoomRow:=0, PrintZoomPaperWidth:=0, PrintZoomPaperHeight:=0
Case vbCancel
Documents(2).Close SaveChanges:=False
Exit Sub
End Select
End If

End Sub

I have a worksheet with cells that contain about 200-3000 characters.
These cells are linked into a Word 2003 document using the

{LINK Excel.Sheet8 C:filename.xls SheetName!namedcell a r f 4}

syntax.

Problem is that my cells are getting truncated at nine
hundred-something characters or so. This does not seem exactly
consistent (I cut pasted text into new Word docs and one was 990
characters including blanks, another 992, another 933).

Does anyone know what the nature of this limit is (I read of a 256 char
limit in 2000 and ealier editions), and whether it can be
modified/worked around without massive redesign? (I've spent literally
about 5 months building this spreadsheet, I'd really rather not have to
start over again.)

Many thanks for any help/thoughts/suggestions.

We have an Excel document which needs to support rather lengthy text entries.
We formatted the cell to wrap the text, but at a certain point, it stops
wrapping.

In the Knowledge base, I've found basic information regarding formatting
cells and it included the following statement.

" Select Wrap Text to wrap the text in the selected cell. The number of
wrapped lines depends on the width of the column and the length of the cell
contents."

So I guess my question is does anyone know how to control the number of
wrapped lines OR is there a fixed limit to the text wrapping in Excel?

Thank you

In Excel 2003 I am entering a paragraph in a cell, but I only have a limited
space to put the information. Is there a way to create a scroll bar on the
side of a certain cell so that I may scroll through information when I need
to? Instead of creating one big cell to show all of the information?

I want Excell 2003 to either limit me from putting a limited of character
within a cell. For example, I want only 10 characters within a cell, so
either I could set it up beforehand and excell will not let me put more than
10 characters or it could count how many characters I have in the cell with a
formula.

How do I set up a cell in MS Excel 2003 to blink? I'm sure it's relatively
simple.

I've got one Excel 2003 file used to maintain a newspaper delivery list.
Four customers have billing needs apart from the norm. I'd like to be able
to make these 4 jump out at me.

I do have very limited experience with Visual Basic in editing a few macros
for a specific MS Excel file used to maintian my pilot's logbook, but that's
it.

Any help would be most welcomed.

Thanks.

We have an Excel document which needs to support rather lengthy text entries.
We formatted the cell to wrap the text, but at a certain point, it stops
wrapping.

In the Knowledge base, I've found basic information regarding formatting
cells and it included the following statement.

" Select Wrap Text to wrap the text in the selected cell. The number of
wrapped lines depends on the width of the column and the length of the cell
contents."

So I guess my question is does anyone know how to control the number of
wrapped lines OR is there a fixed limit to the text wrapping in Excel?

Thank you

Hello,

I am running Excel 2003 and I wish to apply conditional formatting to only cells with format Number. Does anyone knows an easy way of doing this or a VBA code for it?

I have tried on my own to write a code , but as I am new to VBA , I can't figure out how to make it work . The code supposed to compare all the numeric values from an uknown number of columns with numeric values from column B row by row and Bold the ones greater. The number of columns and rows is uknown because the data will be exported from another software to Excel , so bassically I wish to format a woorkbook to automatically do that.
The code is below:


	VB:
	
 ConvertsFontStyletoBold() 
     
    Dim Count As Integer 
    Dim Count1 As Integer 
    Dim nr1 As Range 
    Dim nr2 As Range 
    Dim nr3 As Range 
    Dim nr4 As Range 
    Dim nr5 As Range 
    Dim nr6 As Range 
    Dim nr7 As Range 
    Dim nr8 As Range 
     
     
    Set nr1 = Range("A65536").End(xlUp) 
    nr1.Select 
    Set nr2 = Range("a1").End(xlToRight) 
    nr2.Select 
    Count = Range("A1", nr2).Columns.Count - 1 
    Set nr3 = Range("A1", nr1.Offset(0, Count)) 
    nr3.Name = "Data" 
    nr3.Select 
    Set nr4 = Range("B65536").End(xlUp) 
    nr4.Select 
    Set nr5 = Range("B7", nr4) 
    nr5.Select 
    Set nr6 = Range("D7").End(xlToRight) 
    nr6.Select 
    Set nr7 = Range("D65536").End(xlUp) 
    nr7.Select 
    Count = Range("D7", nr6).Columns.Count - 1 
    Set nr8 = Range("D7", nr7.Offset(0, Count)) 
    nr8.Name = "Values" 
    nr8.Select 
     
    For Each r In nr8.Rows 
        i = r.Row 
        r.Select 
        If IsNumeric(Cells(r)) = True And r.xlCellValue > nr5.xlCellValue = True Then 
            cell.Font.Bold = True 
        End If 
         
    Next r 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thank you very much for any help received.

I have been using http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm to count cells by colour - largely with success.

However, I have encountered a slight compatibility issue whilst using this code. This issue forces Excel 2003 to stop working (and then subsequently recover). The problem occurs when a range of coloured cells which contain wrapped-text are being counted – typically when some of the cells in that range are (or have been) merged.

I was wondering if someone might know how to resolve this issue please.

Kind regards,

Rob

Hello,

i would call myself an ordinary Excel user who tries once a year to
write a formula. This time I wanna concatenate the value of several
cells. I use the values from one sheet, the formula is described on the second one and looks like this:

=IF(AND(NOT(T(Value_Sheet!C2)=""); T(Value_Sheet!B2)="ConditionalValue");
CONCATENATE(T(F2);Value_Sheet!A2);"")

The goal is to create a "space" separated chain of values in case several conditions where met. One condition is that one cell in the "value sheet" has a dedicated value. The second condition ensures that a neighbour cell has at least a value. Pretty simple huh? The result (-chain) should be recorded in the formula sheet. My Problem is that in case all conditions where met as result a Reference error is shown. I don't know why, because the Cell "Value_Sheet!A2" has values in any case which could be used.

My assumption is Excel 2003 with its limited functions cause these reference error. The formula should be valid. Do you agree ?

Cheers, Rumpel

Hi, I can't drag cell contents anymore in Excel 2003. I have already made sure that the box allowing you to drag cell is ticked in the option Menu. Can someone help me? Does someone have another idea what to do? Thanks for your help. Katia

Is there a way to overcome the 65,536 row limitation in excel 2003?

I need to know how can I perform an average for more than 30 cells using Excel 2003.

I've tried to use the average function but I was getting the following:

AVERAGEA(value1,value2,...)
Value1, value2, ... are 1 to 30 cells, ranges of cells, or values for which you want the average.

I would appreciate if you could help me on this

How can I sum by cell color in Excel 2003?

In Microsoft Excel 2003, when I select a color for a cell, it does not show
on the screen> It shows only in the print preview, and when it is printed.
How can I fix this??

I am using Excel 2003. I have a list of addressees that may have been
exported from Outlook. In the address field, there is a return that
separates the street address from the suite number. I need to place this
data into separate cells. How would I do this?