684 kolommen en 127000 rijen, lege cellen verwijderen gaat dagen duren

[love-miranda]

Goedemorgen helpmij.nl

ik heb een gigantisch excel bestand, met voornamelijk lege cellen.
Het was een dump uit autocad tekeningen.

Ik wil alle waarden overzichtelijk hebben in zo weinig mogelijk kolommen verspreid.

Als eerste had ik al een paar duizend regels in andere sheets geplaatst (deze hadden hele andere opmaak)

Nu is het plan dat ik alle lege cellen selecteer en die dan verwijder. De niet lege cellen laat ik dan naar links schuiven.
Maar, het probleem, het gaat een uur of 7 duren om alle lege cellen te selecteren.
Daarna moet ik ze ook nog eens verwijderen wat ook een paar uur zal gaan duren dan lijkt me.



Het excel bestand wil ik bijvoegen, maar het is 5,5 mb dus te groot. Als het goed is kan je er wel bijkomen via we transfer?

https://www.wetransfer.com/downloads/7e3a7915ddc6caab5a53e7718949591420150730091519/24b39f84d004c84e9924855c6e87436b20150730091519/6b9093

Alvast bedankt voor het meedenken!

 

[exhelp]

Je kan het met VBA doen. Kijk eens hier: Rijen verwijderen op basis van een bepaalde celwaarde (VBA)

 

[love-miranda]

Ik zal verder zoeken op vba methodes, maar deze is voor rijen en ik moet
cellen hebben.
Alle compleet lege rijen en kolommen heb ik al weggekregen. Nu zit er ofwel in mijn rij
nog een waarde ofwel in de kolom.
Daarom wil ik van alle rijen de waardes in de eerste kolom schuiven (of in ieder geval de eerste vrije van die regel)

 

[love-miranda]

Oke VBA snap ik dus echt niet Misschien iemand die iets gedetailleerdere hulp kan bieden?

Ik heb wat soortgelijke situaties gevonden, maar moet de code dan toch iets aanpassen, en zie niet wat
in die code dan veranderd moet worden..

 

[Warme bakkertje]

Lege kolommen krijg je al zo weg.

Sub tst()
Application.ScreenUpdating = False
For i = Columns.Count To 1 Step -1
    If WorksheetFunction.CountA(Columns(i)) = 0 Then Columns(i).Delete xlToLeft
 Next
 Application.ScreenUpdating = True
End Sub

 

[Ginger]

Ik had meer iets met deze, maar durf 'm zo niet een zet te geven (ben niet thuis namelijk ).

Sub ff()
Dim i As Long
    
    With Sheets("DataTable")
        For i = 2 To .Columns(1).SpecialCells(xlCellTypeConstants).Rows.Count
            x = Replace(Replace(Application.Trim(Replace(Replace(Join(Application.Transpose(Application.Transpose(.Cells(i, 1).Resize(, 684))), "|"), " ", "^"), "|", " ")), " ", "|"), "^", " ")
            .Cells(i, 685).Value = x
        Next i
    End With

End Sub

Deze procedure doorloopt alle regels van je tabel en plaatst dan in kolom ZI van die regel een met Pipe's gescheiden string. Die kolom kan je dan oppakken en plakken in een nieuwe sheet. Dan even TextToColumns (tekst naar kolommen) toepassen met als scheidingsteken de pipe ( | ). Dan heb je volgens mij het resultaat wat je voor ogen had.
Ik heb dus géén idee hoe lang deze procedure over die meer dan honderdduizend regels doet. Maar zéker sneller dan 7 uur.

Helaas is het totale bereik (UsedRange) veeeeeeeeel te groot om in een array te laden. Anders was het leed zo geleden.

Probeer 'm maar 'ns voorzichtig uit op bijvoobeeld een paar honderd regels. Als de tijd dan meevalt, kan je besluiten om de lijst groter te maken (óf compleet).

 

[MarcelBeug]

Ik heb even een testje gedaan met een paar gevulde cellen waaronder ZH1 (kolom 684) en ZH127000.
Vervolgens via CTRL-g (ga naar), speciaal - lege cellen, alle lege cellen geselecteerd. Dat duurde hooguit een paar minuten.
Dan rechts klikken in 1 van die lege cellen, kiezen voor verwijderen, cellen naar links verplaatsen.
Ik kreeg een boodschap over onvoldoende bronnen, maar heb gekozen voor doorgaan zonder ongedaan te kunnen maken.
En toen was het verwijderen in een oogwenk gepiept.
Misschien heb je hier wat aan.

 

[peter59]

Hallo,

Onderstaand kun je gebruiken voor alles naar links te schuiven naar de eerst volgende kolom.

Selecteer eerst het gehele werkblad dan F5 >> "Speciaal" >> vink "lege waarden" aan >> Ctrl - >> vink "cellen naar links verplaatsen" aan >> OK.
Alles is nu verplaatst.

Mvg
Peter

 

[Warme bakkertje]

@ Leo
101 sec. complete lijst. Heb er wel een on error resume next bovenaan gezet want op rij 107000 (ongeveer) vloog hij eruit.

 

[Ginger]

@Rudi, Dank voor je test. Valt me qua tijd helemaal niet tegen.

 

[snb]

Ik zie weinig problemen met:

Sub M_snb()
  Sheets("Blad2").UsedRange.SpecialCells(4).Delete -4159
End Sub

 

[love-miranda]

Waarschijnlijk zou het bij mij thuis ook super snel gaan, maar helaas ben ik op mijn werk en moet ik het hier doen met de middelen die ik heb.
Mijn backup plan was al om het vanavond thuis te doen

Maar, dat is niet nodig, want de VBA van Ginger is ook supersnel. Misschien een minuut of 2
Alleen kreeg ik na 106000 regels een foutmelding 13 dat de

Maar even iets anders, zoveel mensen met supersnelle computers die het hebben uitgevoerd, en niemand die het verwerkte bestandje terug
wil sturen? :d

 

[Warme bakkertje]

Hebt ge de code van snb al eens uitgeprobeerd ?
Daar hebt ge geen supersnelle computer voor nodig.

 

[love-miranda]

Meteen geprobeerd warme bakkertje

Ben nu een kwartier aan het wachten hahaha

Ga het management toch eens op de hoogte stellen.. Als er een goed computersysteem was geweest had het allang klaar geweest.
Nu ben ik al uren ermee bezig

 

[Warme bakkertje]

Amper 2 min op alle werkbladen in je voorbeelbestand .

 

[love-miranda]

Het is nog steeds bezig.. bijna een uur nu..

Is er iemand die de mogelijkheid heeft het omgezette bestand weer ergens te uploaden?

 

[love-miranda]

Thuis is het ook niet gelukt met de vba code van SNB. Hele weekend stond het op 'running', maar het is niet af gekomen.

De vba code van Ginger lukte wel goed, maar alleen tot regel 107000 inderdaad.

Is er iemand die nog een oplossing weet?

 

[Ginger]

In cel KQ107174 en JN108599 staat een error. Dit is een formule en dat gaat niet goed. Pas die ff aan en mijn procedure loopt als een zonnetje.

 

[love-miranda]

Jaaaa Gelukt!!

Bedankt iedereen!

 

[exhelp]

Als je problemen met de snelheid van je VBA code hebt, kijk dan eens hier: http://www.exhelp.be/vba/1209