Te kopieren bereik te groot

[Plotinus]

L.S.

Een bepaald werkblad in een Excel-bestand wil ik eerst helemaal leegmaken vanaf rij 4, om vervolgens rij 3 tot de laatst gevulde cel omlaag te kopiëren, afhankelijk van het aantal rijen in een ander werkblad. Om dit gemakkelijk te maken heb ik me gewend tot VBA, te beginnen met de macro-recorder en vervolgens de code wat opgeschoond. Zie onderstaand resultaat.

 Aantalrijen = Sheets("LoggedSpectra").Range("P65536").End(xlUp).Row
    Sheets("Tonaal").Select
    Range("A4").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Range(Selection, Selection.End(xlDown)).Clear
    Range("A3").Select
    Application.Goto Reference:="R3C1:R" & Aantalrijen & "C67"
    Selection.FillDown

Dit geeft echter een foutmelding dat de selectie te groot is (fout 1004). Door wat te testen ben ik erachter dat als 67 (zie voorlaatste regel) omlaag wordt gebracht naar 45 het nog juiste wel gaat. Vraag is nu hoe ik dit oplos? Het in twee fases van kolom 1 tot 45 en dan van kolom 46 tot 67 de kopieer-actie uitvoeren geeft - merkwaardig genoeg - dezelfde fout

Ik heb ook al even met een 'For-Next-lus' zitten spelen (en daarmee lukt het om rij voor rij te kopiëren), maar dat vraagt nogal wat tijd bij duizenden rijen, en dan is de bruikbaarheid niet meer zo groot.

gegroet,
mrt

 

[zapatr]

Nogal primitief allemaal.
Vertel eerst eens:
1. Wat staat er in het blad Tonaal in C67 ?
2. Die cel valt toch ook in het bereik dat gewist wordt?
Je moet niet meer rijen/kolommen in je berekening betrekken dan nodig.
3. Welk bereik van rij 3 wil je naar beneden doorvoeren (bv. A3:T3, of een ander bereik)?

 

[Plotinus]

Ik was niet helemaal duidelijk merkte ik al, vandaar mijn tekst iets aangevuld.

Het gaat hier om een actie waarbij de gegevens (verwijzingen en formules) in Rij 3 van A3 tot BO3 naar beneden worden gekopieerd. Dit is eigenlijk wat je handmatig doet met een cel(bereik) selecteren en dan slepen. Omdat dit een directie relatie heeft met het aantal cellen in een andere sheet (LoggedSpectra), moet de kopieeractie worden uitgevoerd voor het aantal rijen dat daar staat. En inderdaad, om het bestand niet nodeloos zwaar te maken, maar precies op maat.
Het gaat dus niet om de inhoud van cel "C67"; dit is slechts de 67e kolom vanaf de oorsprong in deze actie.

Primitief is dit denk ik niet, al kan het vast iets slimmer in elkaar worden gestoken. De macrorecorder voegt nogal eens wat overbodigs toe weet ik uit ervaring.

gegroet,
mrt

 

[zapatr]

Probeer dit eens en laat snel (ik moet nl. weg) het resultaat weten.

Sub macro2()
Dim aantalrijen As Integer
aantalrijen = Sheets("LoggedSpectra").Range("P65536").End(xlUp).Row
With Sheets("Tonaal")
.Range("A4:BO" & .Range("A65536").End(xlUp).Row).ClearContents
.Range("A3:BO" & aantalrijen).FillDown
End With
End Sub

 

[Plotinus]

Dank zapatr, maar het levert dezelfde foutmelding op.

Misschien hefet het er niets mee te maken, maar ik werk in office 2007, onder XP (32 bit), via Virtualbox in Linux (32 bit). Ik ga het eens uittesten onder Vista rechtstreeks.

gegroet,
mrt

 

[HSV]

Doe er eens je bestandje bij met wat je doet en of wat je wil bereiken.

 

[Plotinus]

Dag Harry,

Hierbij een zeer uitgekleed bestandje van slechts 25 regels in 'LoggedSpectra'. In de werkelijkheid (van dit project) gaat het om 1733 regels, maar het kunnen er ook 100.000 zijn. Nu werkt bijgevoegd macro (bovenste!), maar bij grote omvang, zoals 1733 regels niet: selectie te groot.

Door eenvoudig het de 25 regels zo'n 100 keer te kopiëren heb je het probleem te pakken - en hopelijk een oplossing.

gegroet en succes,
mrt

 

[Plotinus]

lijkt niets te maken te hebben met virtueel draaien: rechtstreeks onder Vista zelfde probleem

 

[zapatr]

Dank zapatr, maar het levert dezelfde foutmelding op.

Bij mij komt er geen foutmelding en doet de macro wat hij moet doen (anders zou ik hem ook niet gepost hebben).
Ik werk met Excel 2002.
'aantalrijen' heb ik gedeclareerd als integer, ervan uitgaande dat je macro over geen groter bereik dan 32768 rijen moet worden uitgevoerd. Als dat wel het geval is, moet je die variabele declareren als 'long'.
Je 2007-bestand kan ik in 2002 niet openen, dus over wat daarin staat kan ik niets zeggen.

 

[Plotinus]

nogmaals dank zapatr voor de hulp; we moeten gewoon verder zoeken. En uit ervaring weet ik dat er wel een oplossing boven komt drijven

 

[Bert Jan]

Beste,

Ik heb het bestand bekeken.
Ik begrijp niet helemaal wat de functie van sheet Tonaal is, voorzover
ik het kan zien is de data gelinkt aan sheet LoggedSpectra.

In sheet LoggedSpectra heb ik tot +/- 3000 regels gekopieerd.
Kolom Start Time heb ik als uniek beschouwd.
Vervolgens heb een ik een sheet aangemaakt, met daarin een draaitabel.
Misschien een idee??

Een probleem, ik kan het bestand niet uploaden. Door het aantal regels is het
bestand meer dan 2 mb. Zelfs in rar is het nog te groot.

Groet,
BJ

 

[zapatr]

Lees dit eens: http://support.microsoft.com/kb/832293
Daar is sprake van 8192 rijen, maar als er veel cellen zijn met opmaak, veel gekleurde cellen en de gegevens niet in een aaneengesloten bereik staan (bij jouw bestand allemaal het geval), dan kunnen er veel sneller problemen ontstaan dan bij 8192 rijen.
De methode die je toepast (met een macro in zoveel duizenden rijen formules plaatsen, je hebt het over mogelijk 100.000 rijen) vind ik ook niet aan te bevelen; het is vragen om moeilijkheden. Macro's zijn juist bedoeld om GEEN formules (of toch niet zoveel als jij wil) te moeten gebruiken.

 

[Plotinus]

Beste Bert Jan en zapatr,

Een draaitabel is echt niet mogelijk. Het klopt dat de sheet gelinkt is aan LoggedSpectra. In deze sheet wordt data uit een geluidmeter ingelezen en dat kan in omvang aanzienlijk variëren, tot mogelijk duizenden rijen (het is niet anders). In deze sheet wil ik niet rommelen, dus ik kopieer de van belang zijnde data naar een andere sheet 'Tonaal' genaamd. Vervolgens wordt daar de data geanalyseerd op tonale kenmerken (gezien de complexiteit daarvan voert dat hier te ver om uit te leggen hoe en wat). Feit is dat het werkt en dat de data uit de sheet Tonaal in een andere sheet netjes wordt ingelezen en gerubriceerd en daarmee presentabel is.
Om het geheel gebruiksvriendelijk te maken voor derden, wil ik bepaalde acties automatiseren, zoals deze dus. Handmatig is het een fluitje van een cent, maar via macro's dus niet, tenminste voor Office 2007.

Inmiddels heb ik een trial-versie van Office 2010 geïnstalleerd en daar is er niets aan de hand. Onbegrijpelijk en vervelend als ik daarvoor over moet op die versie die velen nog niet zullen hebben. Ik blijf dus broeden op een andere oplossing.

Overigens wordt opgemerkt dat er in dit geval het al misgaat met 1733 rijen, dus lang geen 8192. En het is waar dat vele duizenden rijen ook anderzijds best voorkomen kan worden gezien de lange rekentijd (matrixformules).

dank voor het meedenken!

gegroet,
mrt

 

[zapatr]

Inmiddels heb ik een trial-versie van Office 2010 geïnstalleerd en daar is er niets aan de hand.

Ik hoop voor je van niet, maar toch denk ik dat je er niet moet van staan te kijken dat ook in Excel 2010 op een onverwacht moment een foutmelding zal verschijnen (afhankelijk van de hoeveelheid gegevens, de opmaak, e.d.). Of en wanneer dat het geval zal zijn, kan niemand zeggen. Je kunt er geen vast getal (bv: bij .... rijen loopt het fout) op plakken, want dat kan op elke computer anders zijn.

 

[cow18]

werkt dit niet beter, hier kopieer je de gegevens uit loggedspectra en plak ze als waarden en daarna ga je kolom per kolom van de formules gaan aanpakken.

Sub macro2()
  Dim AantalRijenLS As Long, c As Range
  AantalRijenLS = Sheets("LoggedSpectra").Range("P" & Rows.Count).End(xlUp).Row  'rijnummer laatste rij in LoggedSpectra
  With Sheets("Tonaal")
    .Range("A4:BO4").Resize(WorksheetFunction.Min(.Rows.Count - 3, .UsedRange.Rows.Count)).ClearContents  'bereik wissen in totaal
    Sheets("LoggedSpectra").Range("P2:BA2").Resize(AantalRijenLS - 1).Copy  'kopieer gegevens uit LoggedSpectra
    .Range("A3").PasteSpecial xlValues                     'plak ze in tonaal als waarden
    For Each c In .Range("AS3:BO3").SpecialCells(xlFormulas)  '1 voor 1 alle cellen met formules in bovenste rij aflopen
      c.Resize(AantalRijenLS - 1).FillDown                 'formule doortrekken
    Next
  End With
End Sub

de matrxiformules lopen maar over een kleine 10.000 rijen, dat moet eventueel nog wijzigen, maar vermoedelijk is dat al een harde pil voor het herrekenen van je blad, hopelijk heb je een krachtige processor ...

 

[Plotinus]

Dat is de oplossing; het werkt cow18 en nog super snel ook.Hartelijk dank!

Dat van die matrixformules over 10.000 rijen had je waarschijnlijk uit de instelling bij de rangordebepaling gehaald. Ik moet het eerst eens testen met zoveel rijen of het nog te behappen valt, anders zet ik er een bovengrens op. Maar het is dus goed mogelijk dat er veel meer data moet worden verwerkt.

gegroet,
mrt