Solved

How do I overcome limit in Excel "Go To Special"?

Posted on 2010-11-21
13
1,304 Views
Last Modified: 2013-11-25
Hello EE Community:
I'm working in Excel (2007) with an export of half a million records, which are not arranged well.  There are myriad records where an extra date is mistakenly inserted, which shifts over the subsequent columns of data.
So, I tried F5, [Go To | Special] to select all the cells with dates.  After that, I planned to delete these cells and shift cells left to bring the rest of my data into alignment.  However, Excel returned the error message "Selection is too large."  What is the limit, and how would I overcome?

Thanks so much,
-Chris
0
Comment
Question by:sinkerballer
  • 6
  • 3
  • 2
  • +1
13 Comments
 
LVL 9

Assisted Solution

by:Tom
Tom earned 25 total points
ID: 34184888

Worksheet and workbook specifications and limits
Feature Maximum limit

Open workbooks Limited by available memory and system resources
Worksheet size 1,048,576 rows by 16,384 columns
Column width 255 characters
Row height 409 points
Page breaks 1,026 horizontal and vertical
Total number of characters that a cell can contain 32,767 characters
Characters in a header or footer 255
Sheets in a workbook Limited by available memory (default is 3 sheets)
Colors in a workbook 16 million colors (32 bit with full access to 24 bit color spectrum)
Named views (view: A set of display and print settings that you can name and apply to a workbook. You can create more than one view of the same workbook without saving separate copies of the workbook.) in a workbook Limited by available memory
Unique cell formats/cell styles 64,000
Fill styles 256
Line weight and styles 256
Unique font types 1,024 global fonts available for use; 512 per workbook
Number formats in a workbook Between 200 and 250, depending on the language version of Excel that you have installed
Names in a workbook Limited by available memory
Windows in a workbook Limited by available memory
Panes in a window 4
Linked sheets Limited by available memory
Scenarios (scenario: A named set of input values that you can substitute in a worksheet model.) Limited by available memory; a summary report shows only the first 251 scenarios
Changing cells in a scenario 32
Adjustable cells in Solver 200
Custom functions Limited by available memory
Zoom range 10 percent to 400 percent
Reports Limited by available memory
Sort references 64 in a single sort; unlimited when using sequential sorts
Undo levels 100
Fields in a data form 32
Workbook parameters 255 parameters per workbook
Filter drop-down lists 10,000


The rest is up to your PC :-)  Loads of ram and a fast CPU
0
 
LVL 50

Accepted Solution

by:
Dave Brett earned 100 total points
ID: 34184924
@ sinkerballer

Where you trying to select all cells that were say Constants & checking Numbers?

The workaround on the 8192 area limit is to break the selection into sub chunks which is more amenable to VBA than manual selection

Debra's site give a sample of this code
http://blog.contextures.com/archives/2010/10/18/excel-error-selection-is-too-large/

we can tailor it if needed

@tru3533

1) In future reference data origins when you copy and paste content
2) That does not overcome the issue, that simply states limits

Dave
0
 

Author Comment

by:sinkerballer
ID: 34184929
I'll keep this for future reference.  It still leaves the pressing quesion about how to overcome, though.  
Thanks, and I'll keep seeking a quick way to overcome the presumed limitation.

I ran a macro for the Go To Special and stepped into so I could try to edit the VB script.  It didn't work and didnt return any errors or anything.  Again, I need this code to work for about a half-million rows.

Thanks,
-Chris

Sub GoToSpecialDate()

'

' GoToSpecialDate Macro

'



'

    Selection.SpecialCells(xlCellTypeConstants, 1).Select

End Sub

Open in new window

0
 
LVL 9

Expert Comment

by:Tom
ID: 34185019
1 Sinkerballer asked:What is the limit,
The limit is taken from Microsofts excel 2007 page and it states: Worksheet size 1,048,576 rows
1 million rows !
2. Sinkerballer have 500 000 rows so this is 50% of the capasity of excel

If you can download a trial version of excel 2010 then all other issues with 8192  should be solved.
So to overcome this peticular problem use excel 2010
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34185036
Chris,

If you post a sample file we can tailor a solution.

tru3533

1) The total row limit is irrelevant. Its a SpecialCells issue
2) No. See 1

Dave
0
 

Author Comment

by:sinkerballer
ID: 34185045
@brettdj,
I followed the link and tried to apply the code.  It was a partial success.  It seemed to delete more than I wanted.  So, I copied and modified the code (below Macro4) to stop at just selecting cells.  I don't understand what the code is doing (honestly) and why the code in DeleteDuplDate is deleting more than I intend.

-sinkerballer
Sub DeleteDuplDate()

'

' DeleteDuplDate Macro

' Range already selected, GoToSpecial, constants=numbers, delete cells and shift left



'

    Dim Area As Range, LastRow As Long, X As Long

    Const Limit As Long = 8000

    On Error Resume Next

    Application.ScreenUpdating = False

    For X = 1 To ActiveSheet.UsedRange.Rows.Count Step Limit

    For Each Area In Cells(X, ActiveCell.Column).Resize(Limit). _

    SpecialCells(xlCellTypeConstants, 1).Select

    Selection.Delete Shift:=xlToLeft

    Application.ScreenUpdating = True

Next

Next

End Sub





Sub Macro4()

'

' Macro4 Macro

' Range already selected, GoToSpecial, constants=numbers, select only



'

    Dim Area As Range, LastRow As Long, X As Long

    Const Limit As Long = 8000

    Rem On Error Resume Next

    Application.ScreenUpdating = False

    For X = 1 To ActiveSheet.UsedRange.Rows.Count Step Limit

    For Each Area In Cells(X, ActiveCell.Column).Resize(Limit). _

    SpecialCells(xlCellTypeConstants, 1).Select

    Application.ScreenUpdating = True

Next

Next

End Sub

Open in new window

0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:sinkerballer
ID: 34185115
Book1.xlsx
The file attached is a sanitized sample of the records in my working file.  The column "ProblemColumn" is the column in which I want to delete only the date entries.  The majority of entries that need to remain are actually text entries (as from the exporting database/ system).  I highlighed the date entries that are the target for deletion.  After deleting, cells should shift left.

The file as exported from a validated quality system have additional problems with alignment, so this is the first of several data cleanups needed.

Thanks again,
-sinkerballer
0
 
LVL 33

Expert Comment

by:Norie
ID: 34188698
What about rows like 39 where you don't appear to need to shift the data left?

In fact the first 3 rows where the column K is highlighted appear to be the only ones where the shift might be required?
0
 

Author Comment

by:sinkerballer
ID: 34189415
It's something atypical at the data input in the database.  I expect I need to delete and shift left all colunm "K" entries highlighted because it's redundant and causes adjacent right data to be shifted and not aligned with the rest of the data table.
What complicates things is multiple misalignments at same time.  At every "User Name" in column "K" the following row contains data that should instead be in the same row as "User Name"
0
 
LVL 33

Expert Comment

by:Norie
ID: 34189782
So how can it be determined what action to take?

If you delete all the highlighted entries and shift left you'll surely end up with some dat in the wrong place?

I've just had another look at the data and it appears there's also something going on in P  & Q of the highlighted rows.
0
 

Author Comment

by:sinkerballer
ID: 34191137
My approach so far has been to identify the cells in column "K" that are constants and numbers.  If I delete these cells and shift left, the data will be aligned.

So the action to take is select cells in column "K" that are numbers and delete these cells shifting left the adjacent cells.

The added problem is the code above takes too long to run on over 400,000 records.  Excel stops responding after 5-10 minutes.  So, a speedier approach is needed.
0
 
LVL 33

Expert Comment

by:Norie
ID: 34192758
Sorry that doesn't quite make sense, am I missing something?

There seems to  rows where that won't work.

I've just had another look at the workbook you attached and I'm even more confused now.

What exactly does this data represent?

Are columns A-J how you want the data and the second (from K) onwards the raw data that's the problem?
0
 

Author Closing Comment

by:sinkerballer
ID: 34300686
Secondary question about why extra data were deleted was not answered.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now