in een range alleen handmatige invoer leegmaken van verder "lege" regels

[origami123]

Goedemiddag,

Ik ben op zoek naar een vba-code, waarmee ik cellen kan leegmaken na handmatige invoer.

Nadat een formule op een ander werkblad de regel heeft leeggemaakt en daarna Autofilter deze regels verbergt, blijft door deze niet gewiste handmatige invoer de verder lege regel in beeld staan.

Ik gebruik nu onderstaande formule, maar die haalt in de range de handmatige invoer weg uit alle, ook ingevulde regels en dat is ook weer niet de bedoeling.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("BB3").Value > "1" Then
        Range("G20:G213").ClearContents
    End If
End Sub

Hoe kan ik in deze vba-code aangeven, dat alleen van de overblijvende verder lege regels de handmatige invoer in een bepaalde cel moet worden gewist.

De hele regels wissen had een optie kunnen zijn, maar omdat ze zijn voorzien van formules, mogen ze echt alleen maar worden verborgen en zeker niet worden gewist.

Bvd.

M.vr.gr.

 

[Roncancio]

Wellicht bedoel je het zo:

Private Sub Worksheet_Calculate()

Range("A1:O20").AutoFilter 1, "<>"

End Sub

Het bereik A1:O20 filtert alle lege cellen in de A-kolom bij een berekening.
Dus stel in cel A14 staat:

=Als(C25="";"";C25)

Maak je cel C25 leeg dan is cel A14 dus ook leeg en wordt regel 14 verborgen.
Vul je cel C25 en wordt cel A14 ook gevuld en verschijnt automatisch regel 14.

Met vriendelijke groet,


Roncancio

 

[origami123]

Hallo Roncancio,

Bedankt voor je reactie.

Jouw oplossing is prima, maar het is - in dit werkblad - niet van toepassing om cellen indirect in vullen, want, zoals je aangeeft moet ik dan via een andere cel de waarde in de "goede" cel krijgen.

Ik kan hier echt niet indirect omheen, omdat dit voor oplopend misschien wel 200 rijen onlogisch is, onoverzichtelijk wordt en daarom ook afgeschermd is.

Momenteel worden de rijen in de kolommen B t/m I gevuld d.m.v. formules in de cellen met waardes uit een ander werkblad en in deze rijen moet in de zichtbare regels, daarna nog - voor de eindberekening - een waarde worden ingevoerd in kolom G, daarbij ieder in zijn "eigen" rij.

Als ik op dit moment het betreffende werkblad open, dan worden de kolommen B en H in een range op ongelijkheid van lege cellen gecheckt en dan komt de lege regel met handmatige inhoud direct in beeld via het Autofilter, want deze regel bevat nog een waarde.

Wis ik nu handmatig de invoer in de cel in kolom G van die regel of zet ik die op 0 (=nul) en dit zelfs indirect, dan komt onmiddellijk de "ClearContents" in actie en wordt ALLE handmatige invoer in kolom G gewist en kan ik opnieuw beginnen.

Als ik de rij op een andere manier uit het zicht wil hebben, dan zijn er natuurlijk verschillende manieren om in kolom A de waarde op 1 te krijgen, waarna het Autofilter de regel onzichtbaar maakt, maar dan zonder de inhoud te wissen en bevindt de handmatig ingevoerde waarde in kolom G zich - hoewel nu onzichtbaar - nog steeds in de cel.

Mocht de regel bij een latere filtering weer in beeld komen, dan moet deze leeg zijn voor nieuwe invoer, maar is dat dan niet.

Bovendien is in dit geval de som van deze cellen uit kolom G niet correct meer.


Ik hoop, dat ik met bovenstaande wat beter duidelijk heb kunnen maken, waar het probleem zit.


M.vr.gr.

Origami

 

[Roncancio]

Volgens mij gewoon een aanpassing van mijn vorig bericht:

Private Sub Worksheet_Calculate()

Range("A1:O20").AutoFilter 7, "<>"

End Sub

De 7 staat voor de 7e kolom (G-kolom).

Met vriendelijke groet,


Roncancio

 

[origami123]

Roncancio

Ik heb de nieuwe code uitgeprobeerd, maar ik krijg de melding:
"onvoldoende geheugen."
"fout 1004 tijdens uitvoeren door toepassing of door object gedefnieerde fout:"
Ik heb de range aangepast en ook de voorwaarden, maar hij blijft komen met bovenstaande foutmelding.

Ik denk, dat de oplossing meer ligt in de sub voor "ClearContens"

Zoals de code nu is opgesteld, wordt bij een bepaalde waarde in een cel, alles zonder uitzondering leeggemaakt in kolom G.
Echter eigenlijk zou in een lus regel voor regel moeten worden gecontroleerd om pas te wissen, als de waarde voor een bepaalde cel aan de voorwaarde voldoet, bijv, zoals hieronder in cel M20. (0 of 1 - ONWAAR of WAAR)

Ik kan me voorstellen, dat de formules uitgesplitst zouden kunnen zijn als bijv. :

- is B20 = gevuld en G20 = leeg - uitkomst in K20: =ALS(B20<>0;EN(G20=0;1;0))
- is B20 = leeg en G20 = gevuld - uitkomst in L20: =ALS(B20=0;EN(G20<>0;0;1))
- uitkomst in M20: =ALS(K20=0;EN(L20=0;0;1))

Deze uitkomst in de cellen K20 en L20 vormen een voorwaarde in één cel in bijv. kolom M voor elke rij.

is die waarde = 0, dan wordt de betreffende cel in kolom G gewist. (ONWAAR)
is die waarde > 0, dan wordt de betreffende cel in kolom G niet gewist. (WAAR)

Hoe deze IF, THEN te realiseren in een Private_Sub?

M.vr.gr.

 

[Roncancio]

Dan kan je filteren op de M-kolom.

Private Sub Worksheet_Calculate()

Range("A1:O200").AutoFilter 13, 1

End Sub

Alle rijen met 1'en in de M-kolom worden getoond.

Met vriendelijke groet,


Roncancio

 

[origami123]

Roncancio

Opnieuw bedankt voor je reactie,

Maar het gaat er eigenlijk om, dat de handingevoerde cellen ik kolom G worden gewist in de rijen, die verder in de andere cellen in de range via formules wèl afdoende zijn leeggemaakt.

Deze handmatige invoer heeft tot nu toe geen formule per regel om automatisch te wissen, dus daar zit de boosdoener.

De filtering, die ik momenteel hanteer werkt ook, daar ligt het dus niet aan.

M.vr.gr.

 

[Roncancio]

Dus er moet gecontroleerd worden of de waarde in de G-kolom handmatig is ingevuld én dat de waarde op dezelfde regel wel leeg (via formule) zijn.
Ik neem aan dat alle waardes in de G-kolom handmatig zijn ingevuld.
Je zou met HasFormula kunnen controleren of er een formule in een cel staat.

Bijv.:

If Range("G1").HasFormula Then MsgBox "Er zit een formule in de cel."

Met vriendelijke groet,


Roncancio

 

[origami123]

Er hoeft niet te worden gecontroleerd of er een formule in de cel zit, want kolom G is verder leeg.

De kolommen B t/m F nemen via formules in de cel gegevens over vanuit een database via een andere sheet, die daar speciaal zijn
geselecteerd.

Voorbeeld: B20: nummer; C20: omschrijving; D20: bijzonderheden; E20: hoeveelheid; F20: behandeling; G20: aantal (Handmatig ingevoerd); H20: totaal (E20*G20).

Als na verwerking, in de rij de cellen B20 t/m F20 en H worden leeggemaakt via de database, dan blijft het handmatig ingevoerde aantal staan.

En juist dit ingevoerde aantal dient automatisch te worden verwijdert of op 0 gezet, zodra de resterende regel wordt leeggemaakt.
Dit, omdat een volgende keer, dat de regel in beeld komt deze cel geen waarde mag bevatten.

 

[Warme bakkertje]

Dus als ik het goed begrijp geeft de formule in kolom H 0 weer als de regel verwerkt is.
Dan zou onderstaande mi moeten lukken.

Sub tst()
    For Each cl In Range("G2:G" & Cells(Rows.Count, 1).End(xlUp).Row)
        If cl <> "" And Evaluate(cl.Offset(, 1).Address) = 0 Then cl.ClearContents
    Next
End Sub

 

[origami123]

Rudi,

bedankt voor je reactie

Inderdaad, zo kan het ook, in plaats van de B-cel kan ook de H-cel worden gebruikt als trigger.

Dus als de H-cel, waarin de formule met som zich bevindt - is leeggemaakt en dus op "" of 0 (nul) is gezet, doordat de inhoud van deze cellen in dezelfde rij op "" zijn gezet d.m.v. een andere formule, dient de inhoud in de G-cel ook op "" of 0 (nul) te komen staan.

Je code zag er veelbelovend uit, maar maakt helaas de G-cel niet leeg en doet verder ook niets zichtbaars.

Ik zie echter nergens een verwijzing naar de - al dan niet lege - H-cel, die de code toch zou moeten triggeren, maar misschien kijk ik ergens overheen?

M.vr.gr.

Origami

 

[Warme bakkertje]

Is het nu een som-formule of een vermenigvuldiging die in de H-kolom staat ?
Als de E-cel leeg is vermenigvuldig je G-cel met nul dus resultaat nul en dat wordt gecontroleerd door de Evaluate-functie. Dus in principe zou dit perfect moeten werken (althans bij mij toch) als jouw informatie correct is. Dus daarom mijn vraag.
Plaats anders een voorbeeldbestand met de gebruikte formules of verduidelijk eens welke formules je waar gebruikt.

 

[origami123]

Rudi,

Bij deze het bestandje.

Wellicht wordt één en ander zo duidelijker.

M.vr.gr.

Origami

 

[Warme bakkertje]

Sub tst()
    For Each cl In Range("H5:H8")
        If cl <> "" And Evaluate(cl.Offset(, 1).Address) = "" Then cl.ClearContents
    Next
End Sub

 

[origami123]

Rudi,

Bedankt, dit is 'm...... deze code werkt prima en de handmatige invoer in de verschillende regels wordt zeer efficiënt gewist.

Er is nog één klein euveltje, wat nog dient te worden aangepast:
de macro moet nu handmatig worden gestart, omdat hij schijnbaar wordt tegengehouden door het Autofilter.

En als ik dit probeer op te lossen door het Autofilter in de macro in te sluiten, dan blijft één en ander direct hangen en doet niets meer.

Direct na het openen van het werkblad dient de macro zijn werk uit te voeren, waarna het Autofilter de regels kan verbergen, zodat er altijd alleen gevulde regels te zien zullen zijn.

Maar nogmaals bedankt voor de oplossing, ik ben zeer geholpen.

M.vr.gr.

Origami

 

[Warme bakkertje]

Zet onderstaande in de bladmodule v/h betreffende werkblad.

Private Sub Worksheet_Activate()
    For Each cl In Range("H5:H8")
        If cl <> "" And Evaluate(cl.Offset(, 1).Address) = "" Then cl.ClearContents
    Next
End Sub

 

[origami123]

Rudi,

Geweldig, het werkt perfect.

Dit is precies, waar ik naar op zoek was.

Mijn dank is groot.


M.vr.gr.

Origami

 

[Warme bakkertje]

Blij dat we je konden helpen.
Markeer je de vraag dan nog even als opgelost.

 

[origami123]

Okee, nogmaals bedankt.

Tot ziens.

M.vr.gr.

Origami