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

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

Select allOpen in new window

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

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

@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

Select allOpen in new window

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

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?

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"

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.

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.

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?

Secondary question about why extra data were deleted was not answered.