Free Microsoft Excel 2013 Quick Reference

Excel row number limit Results

I have some rather large spreadsheets that are difficult or impossible
to import into Excel.

As an example, I have one with 25 rows and around 200,000 columns.
When I attempt to import into Excel I get this error:

"- Excel cannot exceed the limit of 1,048,576 rows and 16,384 columns.
- By default, Excel places three worksheets in a workbook file. Each
worksheet can contain 1,048,576 rows and 16,384 columns of data, and
workbooks can contain more than three worksheets if your computer has
enough memory to support the additional data."

So why am I allowed to have so many more rows than columns? Why not
limit the size by number of cells rather than an arbitrary number? I
can transpose the data in the originating application to make it fit
Excel's limiations, at least in the case.

Also, it seems like there isn't actually a limit on the amount of data
a workbook can have since you can have about 16 million cells per
sheet but as many sheets as you want (I assume there's some other
limit on number of sheets). So why can't I have a single sheet with 32
million cells rather than 2 sheets with 16 million cells?

Seems if Microsoft were going to the trouble of increasing the
allowable size of spreadsheets, why not make them unrestricted in
size?

Sorry, end of rant...

(yes, I should probably be using Access or mysql or something designed
for large datasets, but sometimes Excel is very nice for quick and
dirty stuff. Access has a horrible import filter anyway, so datasets
with many columns can't be imported even if Access itself were capable
of managing he data. I haven't tried any mysql solutions yet.)

I want to be able to hit "end of page" (CTRL END) and have it go no further
than row 100 and column AZ but i cannot figure out how to do it.

I have inherited a large excel file nearly 4000 rows long
The first column is a reference number and can be between 1 and 750
inclusive.

For information, the number repesents an electronic programable key
for use in a computerised fuel monitoring / issue system. The other
columns are used to record what vehicle is set up against each key,
other columns record similar data, ie. user departments, fuel type
etc.

If a fuel key is returned it can be reprogramed with different
references, the details of the original references are kept in the
spreadsheet.

Therefor if a filter is applied to the first column for say key number
5, all rows with the figure 5 in the first column should be displayed.

Unfortunately all rows are not shown, is there a limit to the number
of rows allowed in a spreadsheet. There does not appear to be any
blanks in the first column. If for instance I enter a unique figure in
the first column of the row following the last row this is picked up
when applying a filter

I know excel is not the best way of recording this data but as stated
I inherited the spreadsheet

Many Thanks

Steve

We have an application written in Excel 2002 VBA that deals with
user-selected cells. The user does not select the cells in any specific
order, nor does he select them from a single column as in this case. However,
the cells must be processed in exact row/column order. We have therefor
sorted the selected cells
in that order and are now about to combine them to a Range using the
following code:

Dim rngSel As Range
Dim strRange As String
Set rngSel = Worksheets("Lines").Range(strRange)

' strRange contains the following list of cells, which are selected by the
user. In this case some of them are
' in a contiguous range:
strRange =
"U4,U5,U6,U7,U8,U9,U10,U11,U12,U13,U14,U15,U16,U17 ,U18,U19,U20,U21,U22,U23,U24,U25,U26,U27,U28,U29,U 30,U31,U32,U33,U34,U35,U36,U37,U38,U39,U40,U41,

U42,U43,U44,U45,U46,U47,U48,U49,U50,U51,U52,U53,U5 4,U55,U56,U57,U58,U59,U60,U61,U62,U63,U64,U66,U66, U67,U68,U69,U70,U71,U72,U73,U74,U77,U76,U77,U78,U7 9,U80"

The "Set Range" statement fails with message: "1004 - Method 'Range' of
object '_Global' failed".

However, if "strRange" only holds up to 65 single cell-addreses, the code
executes correct. But as soon there are 66
or more, it fails with the above error message.
(In this particular case we could set the "strRange" to "U4:U80", but that
would not help, as the user could instead
select cells such as: "B2,C3,D4,E7,D8,B9,F10.....etc.").

We have not been able to find any documentation on a limitation in the
number of individual cells you can specify in order to form a Range.

I hope someone can help ?

Thanks,
Thomas

I'm using XP SP2 and Microsoft Excel 11.0 8105 SP2.

After hours of searching and tweaking I give up.

=SUBTOTAL(3,B4:B5000)

The above works fine if placed in a cell but I can't get the same
results in VBA.

These don't work:

X = Application.WorksheetFunction.SUBTOTAL(3,B4:B5006)

X = SUBTOTAL(3,B4:B5006)

I want to use the row count to set the high limit of a FOR loop as the
filter changes the record count.

How do I count the number of filtered rows?????????

Thanks for any Help, Rick

I am an archaeology student and I am trying to make a catalog workbook for
artifacts. We have the artifacts cataloged by bag number and FS#. What I have
done is entered the FS# and bag number as names of the worksheets and then I
total the worksheet on as FS#-T page. However, occasionally we encounter a
new type of artifact and have to add a new row for the artifact. Thus, we
select all the sheets and add a new row to the catalog form. However, this
affects the formula sheet, and the summation formula does not appear in the
new row. Is there a way to make the column that is adding information for the
separate worksheets equal the formula so that when we insert a new row for a
new artifact, we do not have to go back and copy and paste the formula. Right
now, it is not problematic, but in the summer I will be dealing with a large
number of worksheets.

Also, I noticed that the sum function has a limit of 30 numbers. I know that
at some point I may exceed this in my summations, particularly when I create
a page that totals the results from all the FS#'s (we currently have over 500
FS#'s). Do y'all have any suggestions on how to deal with this?

I have a large data set the needs about 800-900 columns in a worksheet. I
limit for columns in excel is a about 200-300. i dont need any more rows
added, just columns.

Yes, I would definitely like to see 366 Columns.

"Gord Dibben" wrote:

> Paul
>
> Why?
>
> Most people would like to see 366 so's they could have one for each day in the
> year.
>
> Sometimes you have just too many rows to transpose.
>
> Gord Dibben Excel MVP
>
> On Wed, 13 Oct 2004 15:56:59 +0100, "Paulw2k" > wrote:
>
> >Hi
> >
> >My immediate thought is why? Why do you need to use so many columns?
> >
> >The number of columns - 256, is fixed, and for most purposes that is more
> >than sufficient.
> >
> >Are you using the sheet as a database? Better to use a proper database such
> >as Access.
> >
> >Is it a database download? Split the download, with a common field in each
> >sheet. Use
> >summarising table, consoldation tables, pivot-tables... to analyse the data.
> >
> >If the table is using dates in the first row, transpose the table and work
> >row-wise.
> >
> >Regards
> >Paul
> >
> >
> >
> >"Confucious" > wrote in message
> ...
> >>I need to extend an excel spreadsheet past the last available column. There
> >> seem to be loads of rows in an excel sheet but the number of columns is
> >> quite
> >> limiting for what I need. I presume the number of columns is limited for
> >> memory reasons but I can lose some rows if that helps. Any ideas?
> >
>
>

The following quote is posted on behalf of a client using Windows and
Office 2003 Standard. Please email responses directly to me, too,
since I do not read this group much.

Any help/insight would be most appreciated. I'll do my best to relay
any questions people have and bring back any answers to said questions.

The Goal:
In an effort to come up with a new way of looking at organizational
structure, I employed Excel's 'Donut Chart' function. My goal was to
have the head of the organization in the middle of the chart and then
that person's direct reports represented by the inner-most ring of pie
slices. Those people's direct reports would be the next ring of slices
and so on until the outermost ring of pie slices represents those
individuals lowest down in the organizational structure with no
supervisory responsibilities.

Ideally, each slice of the donut chart would be labeled with the
person's name and job description. The largest facility that I was
looking at was approximately 3,000 people. You may wonder why I would
seek such detail when the names and information would be too small to
read when the chart is printed out. The answer is that I have had
individuals in engineering print out very large versions of the chart
(several feet square) that would make each sliver of data legible. The
goal being that someone could spread out this 'poster' and see his
entire organization and the flow thereof in a single view.

Source Data:
I arranged the hierarchical data within excel such that each individual
was a row of data. Each data series represented a level of the
organizational structure. For example: the facility VP may have been a
level 4 and a product assembler at that facility a 9 or 10, with
managers, supervisors, etc. layered in between. The structures varied
somewhat from facility to facility. I arranged the data on the
spreadsheet by offsetting the rows for each particular level. As a
result, the data range for level 5 individuals would go from, say, D1 to
D3000 and just pick up those individuals on that level. The next
series, level 6, would be E1 to E3000 and so on. In order to synch up
all the layers of slices, the values for each data point had to be the
number of people at the lowest level who report to that individual. For
example: if a manager (1 person) has 5 direct reports and each of those
5 individuals has five direct reports, then the value for that manager
needs to be 25. The value of each of his direct reports would be 5 and
the value of each of their direct reports would be 1, assuming that no
one reported to them.

I also arranged for the chart to represent blank slices if someone
higher up in the organization had no direct reports (example: a senior
level engineer with no supervisory responsibilities). I did this by
inserting dummy inputs (value of 1) in the series for each lower level.

The Problem:
The problem arose not in arranging the data inputs for the chart but in
excel.s creation of the chart itself. Given the finite nature of a
circular chart, excel appeared incapable of representing every data
point. It is my belief that the source of the problem was the sharp
contrast in values that would be presented in any given data series.
For example: a single production manager may have 500 people reporting
to him, either directly or indirectly. That same manager may be a
'level 6' within the organization. Working right alongside that manager
may be a senior manufacturing engineer who is also a level six. That
senior engineer may have a single person reporting to him. Such
contrasts within the organizational structure were frequent (part of the
reason we wanted to take a look at it). What would happen is that excel
would recognize the manager's slice with a value of 500 but that the
single slice of the senior engineer with a value of one would not even
show up on the chart.

The Solution:
I spent a great deal of time looking at excel and its limitations. I
was unable to identify a clear statement within its limitations that
legitimized this failure. Nonetheless, I moved on. I took several
corrective actions to complete the project within excel. The first
action was to re-focus the scope of the organizational structure that I
represented. For instance, rather than start with the Facility Head in
the center of the chart, I started with the Director of Manufacturing,
who worked for the guy who worked for the facility head. This limited
the number of overall individuals from 3000 down to 2200. The other
action I took was to ignore all individuals who had no supervisory
responsibilities. This essentially removed the outermost ring of the
chart, which is a good thing because excel proved entirely incapable of
accurately depicting the hundreds of tiny slivers who were low in the
organization. Yet excel still was not representing every data point
that I identified. The last action I took was to 'fudge' the data
inputs. If a data point of, say, 1, proved too small to show up on the
chart then I would change that input to perhaps a 5, or whatever value
was necessary to have it show up. To offset this addition of 4 units, I
would subtract 4 from another point within the series, preferably one
with a large number like 500 so that the scale would remain generally
intact.

The end result was a finished product less comprehensive and more labor
intensive (keeping all the fudges straight was a pain in the neck) than
originally planned; a compromise.

Go to Excel Help menu, type in limits
"Worksheet size 65,536 rows by 256 columns"
Asking for more columns or rows is like standing on the Superdome and asking
Katrina to stop and go back where it came.

"Petronella Halldin" wrote:

> Hi, I have a basic queastion, but I can't find any given limitations written
> anywhere. I have managed to group, in two levels, columns from "H" to column
> "DA". I have more material that I want to group, but the work sheet wont
> accept my request.
>
> What happens: I CAN make the group, but when i press the "+"-sign to group
> them, I can't. I get a pop-up saying something like: "objects cannot be
> moved outside work sheet". If a open other groups, I can close later groups,
> but as I write, not all groups.
>
> Who has the answer to this one? Thank you all for you time!
>
> ---
> Petronella

Hi -
My Excel sheet has two columns that represent minimum and maximum
income limits - looks a little like this:
Column A Column B
1 3
4 6
7 10
11 13

and so on up to 3800 or so. I want users to be able to enter a number,
and be taken to the row that corresponds; for example, if a user enters
12, I want them to be taken to the row where column A = 11 and column
B=13.
I've managed to come up with the VLOOKUP statement to find the closest
matching number in column A (VLOOKUP("data entry cell",A3:B1162, 1,
TRUE)), but I can't figure out how to use the result of the VLOOKUP in
a Find function to take the user to the appropriate row.
Any help would be appreciated.
Ginger

--
Ginger
------------------------------------------------------------------------
Ginger's Profile: http://www.excelforum.com/member.php...o&userid=27034
View this thread: http://www.excelforum.com/showthread...hreadid=442769

I'm trying to modify John Walkenbach's code for creating an option-button
UserForm programmatically (http://j-walk.com/ss/excel/tips/tip76.htm). He
shows it as creating a Form with an array of option buttons for each month,
and it works great. I am trying to modify it to create an array of option
buttons for each row of a table, when the number of rows is not known.

John's code is:
Sub DemoGetOption()
Dim Ops(1 To 12) As String
' Create an array of month names
For i = 1 To 12
Ops(i) = Format(DateSerial(1, i, 1), "mmmm")
Next i
UserChoice = GetOption(Ops, 1, "Select a month")
MsgBox UserChoice
End Sub
I tried:
Sub DemoGetOption()
cnt = tbl.Rows.Count
Dim Ops(1 To cnt) As String
but the code errored, saying I need a constant as the second term in the
array declaration.

I also tried:
Sub DemoGetOption()
cnt = tbl.Rows.Count
Dim Ops(1 To 999) As String
For i = 1 To cnt
Ops(i) = doc2.tbl.Cell(i,1).Range.Text
Next i
UserChoice = GetOption(Ops, 1, "Select a month")
MsgBox UserChoice
End Sub

That worked, but since I only have five rows, it gave me lots of empty
option buttons!

How do I either (a) declare my array limits using the "cnt" varable?, or (b)
limit the number of option buttons to only those used?

Ed

My Access 2002's export interface (ISAM) seems to be associated with earlier
versions of Excel and limits the numbers of rows that can be exported to
16,000. How do I fix it so that I can export more rows?

Hi,

I made a sheet that has a few simple calculators on it, and I would like to
limit the size of the excel sheet to only what needs to be viewed, say 20
columns and twenty rows.I would also like to be able to get rid of the column
and row numbers and letters. Any help will be appreciated, I realize these
may be simple tasks but I can't find the answers anywhere.

Thnak You,

I am trying to make a form and I can't figure out how to display a limited
number of rows or colums. For example I only want there to be 30 rows
instead of the infinite number by default. I have a file that is like this
so I know it can be done, I just don't know how to get there.

Okay,

I know that until 2007 excel has a max allowed rows of around 65536. I have
read answers here that that cap is no long applied to excel 2007. What is
the cap in 2007 or what is the limiting factor now in excel 2007 for the
number of records/rows one can have in a worksheet.

I have a function that runs through data and breaks up, sums, and groups
different information (big thanks to Hans for helping me out with it).

Now i would like to make it more user friendly. Before i had the
column numbers hard coded into the formula, but now people in the
office are starting to use the function for different data sets where
the columns are different. I created a simple form were people can
enter the number of the column.
row start = 2
group by col = 5
sum value of col = 11

the problem is that people have a hard time figuring out that column K
is 11, and would much rather type in K into the form then 11.

is there a quick and easy way (or possibly a built in function in
excel) that i can do this.
I realize i could build a giant Select Case statement, but i just think
there has to be a better way.

Another quick question: is there a way to create an input rule in excel
forms similar to Access, where i can limit what a user inputs, for
instance, the row text box can only be numbers, and the column can only
be letters.

thanks

--
trav
------------------------------------------------------------------------
trav's Profile: http://www.excelforum.com/member.php...o&userid=31420
View this thread: http://www.excelforum.com/showthread...hreadid=514461

Rather than over 65,000 rows being available when I open a new worksheet, how
can I limit the number of rows to, say, 200 without having to highlight rows
200 through 65,000 and hide them?

Hi
goto 'tools - Options - Edit' and uncheck 'Fixed decimals'

--
Regards
Frank Kabel
Frankfurt, Germany
"squire" > schrieb im Newsbeitrag
...
> I've been using Excel 2000 to a limited extent for about 2 months on a Win
> XP machine. But the spreadsheets have recently begun behaving peculiarly..
> Whether I'm writing to a completely new sheet or amending an existing one.
>
> If I type 1 it displays in the cell as 0 and in the edit box as 0.01
> If I type 10 it displays in the cell as 0 and in the edit box as 0.1
> If I type 100 it displays in the cell as 1 and in the edit box as 1
> If I type 1000 it displays in the cell as 10 and in the edit box as 10
>
> However, if I put the cursor in the edit box in the 4th example above,
> the 10 is immediately aligned left in its cell. Upon pressing enter, the
> cell then re-aligns to the right as 0 and the edit box changes to 0.1
>
> I've tried running Norton AV and Trend Housecall but no viruses were
> discovered. I've also tried formatting both rows and columns and the
> actual cell without success. No styles are in use.
>
> Help!!!!!
>
> --
> squire

Hi all-

I'm having a problem with the VLookup function. I have an excel file
which automatically fills out several fields in a row when a certain
cell from that row is filled. The 'key' off of which the other fields
key is filled via a drop-down data validation list. The 'subjugate'
cells then are filled with values using the VLookup function. All of
this works to this point. However, I also use the VLookup function in
the coding for the sheet in order to automatically hyperlink some other
relevant data. Some of the choices from the dropdown menu give me an
error, specifically:

Run-time erro '1004':
Unable to get the VLookup property of the WorksheetFunction class

What is perplexing is that only certain choices result in this error,
whilst others work exactly in the method they are supposed to, the
VLOOKUP Formulas in the cells always 'grab' the correct text
information. Other choices from the cell have the right text displayed,
but an incorrect hyperlink. If it makes any difference, the locations of
the data from the list are formatted as 'General' text, and while some
of the choices are only numbers, some of them are numbers AND letters,
and it seems to be predominantly the selections that contain numbers
and letters that have error issues. (For example, a selection of '115'
from the list results in a correct listing of text display in the
'subjugate' cells as well as correct hyperlinks, whereas a selection of
'1035Zn' results in a 1004 error) Is this an artifact of the inherant
nature of the VLOOKUP function itself, or is it something that I'm
doing wrong?

Here is the code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 2 Then
Cells(3, Target.Column).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:=WorksheetFunction.VLookup(Cells(2, Target.Column),
Range("A131:C200"), 3)
Cells(2, Target.Column).Select
Cells(6, Target.Column).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:=WorksheetFunction.VLookup(Cells(2, Target.Column),
Range("A131:G200"), 7)
Cells(2, Target.Column).Select
Cells(4, Target.Column).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection,
Address:=WorksheetFunction.VLookup(Cells(2, Target.Column),
Range("A131:H200"), 8)
Cells(2, Target.Column).Select
Cells(5, Target.Column).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection,
Address:=WorksheetFunction.VLookup(Cells(2, Target.Column),
Range("A131:H200"), 8)
Cells(2, Target.Column).Select
End If
End Sub

Thanks in advance for the help.

--
chris_manning
------------------------------------------------------------------------
chris_manning's Profile: http://www.excelforum.com/member.php...o&userid=23986
View this thread: http://www.excelforum.com/showthread...hreadid=393454