Verwijderen van een groep van 11 cellen waarbij de identifier in de 7e kolom staat

[Humadgen]

Hallo,

Ik ben op zoek naar een oplossing voor het volgende probleem:

Ik heb een spreadsheet met daarin een heleboel data. (6000 regels, 120 kolommen)
De data in de eerste 10 (maar soms de eerste 15 kolommen) staat vast en daar mag niets mee gebeuren.
Vervolgens komen er steeds kolommen met blokken van 11 kolommen met data.
In de 7e kolom bij ieder blok staan getallen.
Aan het eind van het spreadsheet staan 5 kolommen waarvan de data niet mag verdwijnen.

Per rij en per blok moet er nu worden gekeken of in die 7e kolom (van een blok van 11) het getal 0 staat. Indien dat zo is dan moeten op die regel de 11 cellen uit het blok verwijderd worden.

Het probleem waar ik voor sta is 2 ledig:
1. Is er een mogelijkheid (bijvoorbeeld met een invul window) om te starten met een zoek actie in de 11e of 16e kolom?. (maar mag ook in de code zitten)
2. Hoe kan ik iedere keer naar de 7e kolom laten zoeken naar de waarde 0, en indien die gevonden wordt, op een regel de 6 cellen ervoor, de bewuste cel met 0 erin en de vier cellen achter de 0 verzijderen.

In het ZIP filetje zie je in sheet 1 een voorbeeld van hoe hoe de data er ongeveer uitziet als ik het ontvang, en in sheet 2 hoe het eruit zou zien als de bewuste cellen zijn gedelete.
De stukken die ik zou moeten deleten heb ik in sheet 1 rood gekleurd.

Als iemand me met een stuk code kan helpen graag, want de tijd die het me nu kost om het handmatig te doen kan ik beter besteden.

Vriendelijke groet
Humadgen

 

[Wigi]

En wat heb je zelf al gedaan? Ik bedoel, meer dan een paar lussen heb je hiervoor niet nodig. De code die je nodig hebt, is echt niet het einde van de wereld hoor.

 

[Wigi]

meer dan een paar lussen heb je hiervoor niet nodig.

Om mijn argument wat meer vorm te geven:

je moet een lus maken door de cellen van kolom A. Dus net zolang als er niet-lege cellen zijn. Bvb.

Dim r As Range
    For Each r In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
        ' code
    Next r

Vervolgens zet je daarbinnen nog een 2de lus, die gaat van 11, 22, 33, 44, ..., 121 (of tot hoever nodig). Als het bij 15 begint: 15, 26, 37, 48, ..., 125.

Hopelijk is dit duidelijker.

Wigi

 

[Hans123]

plak volgende code in je werkblad en voor deze uit.

Sub verwijderen()

Dim start_kolom As Integer
Dim r As Integer

Sheets("sheet1").Activate

start_kolom = CInt(InputBox("geef nr van je eerste datakolom op als getal" & Chr(10) & " (kolom a=1, kolom b=2 enz)"))

For rij = 1 To Range("A65000").End(xlUp).Row
    
   kolom = start_kolom + 6
    
   Do While Not Cells(rij, kolom).Value = ""
        If Cells(rij, kolom).Value = 0 Then
              Range(Cells(rij, kolom - 6), Cells(rij, kolom + 4)).Delete Shift:=xlToLeft
        Else
            kolom = kolom + 11
        End If
    Loop

Next rij

End Sub

 

[Humadgen]

Wigi en Hans123

Allebei weer heel erg bedankt voor geboden hulp.:thumb:
(Hoe bevalt je nieuwe baan Wigi?)


Wigi

Ik ben aardig handig in EXCEL en kan in VBA macro's opnemen en teruglezen en begin steeds beter te begrijpen wat er nog meer kan in VBA. (door af en toe vragen weg te zetten op Helpmij en ook door te proberen in andere Forum stukken te lezen, zie ik wat jullie "goeroes" voor oplossingen in VBA wegzetten)

Ik weet nu wel hoe ik simpele lussen kan maken, (opvullen van kolommen met waardes) Ik wist inmiddels hoe je een keuze window kon maken, maar als het gecombineerd moet gaan worden van lussen binnen lussen en dan ook nog "communicatie windows" er tussen, dan loop ik nog vast.

Je vraag: "wat heb je er zelf al aan gedaan", was dus simpel te beantwoorden door: "Nog niets, want ik wist niet hoe te beginnen."
Op zich begrijp ik je vraag wel, want het Forum moet meer zijn dan: "Ik heb een vraag, ik ga achterover zitten en wacht tot het antwoord kant en klaar op mijn bordje komt te liggen."

Je tweede mailtje maakt al wel duidelijk wat het is dat er moet gebeuren, maar als ik dat dan teruglees dan loop ik vast als ik moet bedenken waar ik precies de 2e lus moet zetten. Maar ik leer er wel weer wat mee.


Hans123

Oplossing werkt perfect :thumb:
Maar als ik dat dan weer vergelijk met hoe Wigi begint, dan denk ik dat Wigi op een andere manier begint met zijn oplossing dan dat jij het doet. Als ik dan probeer mijn beperkte VBA kennis er op los te laten, dan loop ik toch vast om precies te begrijpen hoe de oplossing van Wigi er uit zou gaan zien.

Nu zullen er ongetwijfeld meerdere wegen tot de oplossingen leiden en ik heb ook al vaker perfecte oplossingen van Wigi gekregen, maar nu ik de kant en klare oplossing heb gekregen wil ik eigenlijk meer weten over hoe zo'n oplossing eigenlijk moet worden opgebouwd.

Hebben jullie daar een handvest voor en dan heb ik ook nog het probleem wat er nu precies wel en niet in een VBA code mag staan.
Ik begrijp steeds dat VBA in principe in het Engels is en ik werk met een Engelse versie van Excel,
maar als ik dan de oplssing van Hans analyseer, dan zie ik in de lus:

If Cells(rij, kolom).value = 0 Then.

en dan loop ik vast want ik zou niet de woorden rij en kolom verwachten. Mag dan op zo'n moment (en wat is dan dat moment) opeens wel Nederlands gebruikt worden.

Jullie zien, Ik moet een stukje basis kennis hebben van wat nu wel en niet mag en kan in VBA. Dus mochten jullie goede suggesties hebben om ook dat te verbeteren.........

Beiden alvast heel erg bedankt,.
Humadgen:

Ik zal even wachten met het sluiten van deze vraag tot jullie hier op hebben kunnen reageren.

 

[Hans123]

Humadgen,

Zoals je al opmerkte zijn er veel wegen die naar rome leiden. welke je neemt hangt af van persoonlijke voorkeur.
De methode waar wigi mee begint wijkt niet zo heel veel af van mijn begin hoewel het er iets anders uitziet.

de VBA-code is inderdaad engels, de variabelen die je gebruikt (in het voorbeeld rij en kolom) zijn zelf te kiezen. Voor de leesbaarheid is het dan (voor mij) makkelijk om daar nederlandse namen voor te gebruiken. Row en Column had in dit geval waarschijnlijk niet gewerkt als variabele, VB gebruikt deze zelf al.

De variabelen had ik net zo piet of klaas kunnen noemen.

 

[Wigi]

In bijlage mijn oplossing.

Wigi

 

[Humadgen]

Hallo,

Zoals eerder gezegd de oplossing van Hans123 werkt al perfect en ondanks dat ik beginner ben in het lezen van VBA begrijp ik wel wat iedere regel doet
Ik loop alleen tegen het probleem aan dat er door formules ook bedragen zjin die tussen 0 en 1 cent waarde hebben, Met het handje zie je die als 0.00 staan:shocked: , maar EXCEL hekent hem als 0.0000046372 dus geen 0. Ik moet dus het stuke van de code:

If Cells(rij, kolom).Value = 0 Then

Aanpassen, in iets wat zou lijken op

if Value tussen 0 en 0.01

Ik ben al op verschillende manieren gaan gochelen maar heb de juiste combinatie nog niet gevonden. Voor jullie waarschijnlijk een peuleschilletje. Zou een van jullie me dat ook nog kunnen geven.


De oplossing van Wigi heb ik al wel proberen te lezen, maar nog niet kunne ontrafelen wat er nu precies gebeurd, maar daa wil ik in het weekend eens dieper naar kijken.

Mochtyen jullie nog tips hebben (websites of zo) waar ik VBA beter (van start af aan) kan leren dan zie ik die ook met liefde en plezier verschijnen.:thumb:
Thanks
Humadgen

 

[Wigi]

If Round(Cells(rij, kolom).Value, 2) <= 0.01 Then

Wigi

 

[Humadgen]

Wigi

Als ik de code regel (in de code van Hans123)

If Cells(rij, kolom).Value = 0 Then

vervang door

If Round(Cells(rij, kolom).Value, 2) <= 0.01 Then

Dan loopt mijn Macro vast.

Ik heb even bijgevoegd:
1. Een input voorbeeld
2. Het Macro PROGRAMMA wat ik vooro dit probleem ga gebruiken
In het VBA gedeelte staan heel veel commentaar regels bijgeschreven om te kunnen begrijpen wat iedere VBA regel doet. (Mocht je daar fouten in zien, aarzel niet om die te verbeteren, daar leer ik alleen maar van)
3. De output zoals die met de originele code van Hans123 wordt geproduceerd.
Hierin zit
a. een tab met de output van het programma
b. een tab met het handmatige resultaat.
c. een tab waarin aangegeven staat waar het verschil ontstaat.

Opzich doet de oorsponklijke Code perfect wat ie moet doen, maar zoals eerder gezegd moet alles met waarde gelijk en groter aan nul, maar kleiner dan 0,01 geidentificeerd wroden.
us: 0,00 <= x < 0,01

Je zult zien dat de cellen die ik bij het Input voorbeeld ROOD heb gekleurd de verschillen veroorzaken.

Hopelijk kun je de puzzel nu voor me completeren.

Thanks
Humadgen

 

[Hans123]

Als je wilt dat de cellen met een waarde kleiner dan 0,01 ook worden gewist kun je de regel

 If Cells(rij, kolom).Value = 0 Then

vervangen door

 If Cells(rij, kolom).Value < 0.01 Then

Bij mij werkt dat goed.

 

[Humadgen]

Ha Hans123

Bedankt voor je reaktie
Die variant had ik inderdaad al geprobeerd, maar negatieve bedragen moeten wel blijven bestaan vandaar dat de range moet zijn:

0=< x < 0


Voor die range lukt het mij niet om een code te bedenken.

Grtnx
Humadgen

 

[Hans123]

Volgende zou dan kunnen werken:

 If Cells(rij, kolom).Value < 0.01 AND Cells(rij, kolom).Value > -0.01 Then

 

[Humadgen]

Hans123

Het leven kan zo mooi, en oplossingen voor problemen zo simpel zijn.
Werkt inderdaad precies zoals bedoeld.

Ik heb zelf een aantal combinaties geprobeerd, maar deze vreemd genoeg niet.
Goed van je dat je inderdaad ook > -0.01 meeneemt, want daar had ik weer net niet aan gedacht.

Thanks
Humadgen

 

[Wigi]

- als je bestanden uploadt, zorg er dan voor dat er geen links naar andere bestanden zijn. Want dan krijgen anderen foutmeldingen op hun PC als ze het bestand openen.

- ik zie mijn code niet staan (kan zijn dat ik erover gekeken heb).

- Haal regels zoals met

ActiveWindow.SmallScroll

of

.Select

er zoveel mogelijk uit. Die vertragen enkel maar de code.

Wigi

 

[Humadgen]

Ha Wigi

Ik had de Code van Hans123 al gebruikt en die werkte goed van daar dat ik niet meer naar jou code ben overgegaan.
Bovendien begreep ik in de code van Hans wat er gebeurde en kon ik de code die jij had aangeleverd nog niet helemaal doorgronden.

Desalnietemin heel erg bedankt voor je hulp, en ik zal je laatste tips ook meenemen voor de toekomst.:thumb:

Grtnx
Humadgen