Re: Excel 2003 (VBA) fails to delete selected rows

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Ken Wright (ken.wright_at_NOSPAMntlworld.com)
Date: 03/21/04 

Date: Sun, 21 Mar 2004 08:36:40 -0000

Oh boy, if I had the brains of a bunny rabbit then I would have realised that,
especially given that I often use that feature to fill in the blanks in the
source data of a Pivot Table with 0s to avoid any of the automatic fields
subtotalling with COUNT instead of SUM. Occasionally when I do this, I get the
'selection too large' message, and just never dug into it, but did in fact do it
in a couple of operations instead of one. <grrrr>

Still, at least now I know *why* I was getting that error - Cheers again Tom/Ron
:-) 

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03
----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
"Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message
news:%23taiyntDEHA.3236@TK2MSFTNGP09.phx.gbl...
> Hi Ken
>
> David have some notes about it also on his site
> http://www.mvps.org/dmcritchie/excel/delempty.htm#testdata
>
>
>
> -- 
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
news:OnMDWatDEHA.3804@TK2MSFTNGP09.phx.gbl...
> > I just learned something - cheers Tom.
> >
> > Ron, thanks for the KB :-)
> >
> > -- 
> > Regards
> >            Ken.......................    Microsoft MVP - Excel
> >               Sys Spec - Win XP Pro /  XL 97/00/02/03
> >
> > ----------------------------------------------------------------------------
> > It's easier to beg forgiveness than ask permission :-)
> > ----------------------------------------------------------------------------
> >
> >
> >
> > "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> > news:105pb5e5a48ut06@news.supernews.com...
> > > I believe specialcells is limited to 8192 distinct ranges.
> > >
> > > In xl95 and earlier, worksheets had only 16384 rows - so the worst case
> > > would be every other row - thus 8192 areas.  This appears to be a hold
over
> > > from earlier versions.
> > >
> > > -- 
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Dennis" <dmburgess@removespamameritech.net> wrote in message
> > > news:0s5p509m8cu96vqbtijbcljg8qss2ni8g5@4ax.com...
> > > > Ken,
> > > >
> > > > Thanks for your reply.
> > > >
> > > > The issue continues.  If I limit, the records, this attempt to about
> > > 5,000, all works
> > > > as expected - with your code or mine.
> > > >
> > > > Every time I attempt the full 25000 records,  the VBA bombs and deletes
> > > all records!
> > > >
> > > > Possibly a workaround for a RAM memory issue?  (at 256MB should not be a
> > > problem?)
> > > >
> > > > What is the easy way to delete 5000 records at a time until all selected
> > > (via
> > > > auto-filter) are deleted?
> > > >
> > > > "Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote:
> > > >
> > > > >Try this:-
> > > > >
> > > > >On Error Resume Next
> > > > >Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
> > > >
> > >
> > >
> >
> >
> > ---
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.634 / Virus Database: 406 - Release Date: 18/03/2004
> >
> >
>
>
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.634 / Virus Database: 406 - Release Date: 18/03/2004
Quantcast