[Excel] Voorwaardelijk verbergen van rijen met macro

[Verwijderd]

Ik wil graag rijen voorwaardelijk verbergen in Excel 2010. Ik heb wat gezocht op internet en ben er inmiddels achter dat dit enkel met een Macro kan. Hier heb ik helaas geen verstand van, vandaar dat ik jullie hulp in wil schakelen.

Het werkblad waar ik dit wil toepassen (Blad1) staat vol met ALS-formules en verwijzingen naar andere werkbladen. In kolom A staan ALS-formules welke allen een 1 of een 0 terug geven. Wanneer er een 1 gegeven wordt, moet de betreffende rij verborgen worden. Wanneer er in het bestand iets verandert waardoor de 1 weer een 0 wordt, moet de rij weer zichtbaar worden.

Na wat Googlen heb ik de volgende macro gevonden:

Private Sub Worksheet_Calculate()
Dim c As Range
Application.EnableEvents = False
For Each c In Intersect(Columns(1), ActiveSheet.UsedRange)
If Len(c.Value) > 0 And c.Value = 1 Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next
Application.EnableEvents = True
End Sub


Ik heb de macro op volgende wijze toegevoegd: rechter muisknop op Blad1 > programmacode weergeven > paste in het invoerveld

De macro doet wat hij moet doen, ik ondervind echter twee problemen:
1. Het bestand wordt er traag van. Iedere keer als ik iets in een cel invoer moet Excel een paar seconden denken voor ik verder kan (ook als de invoer geen enkele invloed heeft op de formules in kolom A).
2. Zodra ik naast dit Excel bestand nog een ander Excel bestand open, krijg ik de volgende foutmelding van Microsoft Visual Basic:
Fout 1004 tijdens uitvoering:
Methode Intersect van object_Global is mislukt

Verdere info die wellicht van belang is:
- Het grootste gedeelte van de werkmap is beveiligd, op een aantal invoercellen na. Sommige van deze invoercellen bevatten keuzelijstjes m.b.v. gegevensvalidatie.
- Het blad bevat een aantal selectievakjes, welke WAAR of ONWAAR terug geven op Blad2.
- Diverse cellen bevatten voorwaardelijke opmaak.

Dus mijn vraag luidt: Hoe moet ik de macro aanpassen om genoemde problemen te verhelpen?

[F_J_K]

[ot]De code wordt wat beter leesbaar als je [code] ... [\/code] tags gebruikt. Ik ben zo vrij geweest je post aan te passen.

Worksheet_Calculate() 'vuurt' bij iedere keer dat er een herberekening plaatsvindt. Dat is vaak. Je wilt misschien OnChange(https://support.microsoft...ain-cells-change-in-excel) gebruiken (wat iets minder vaak voor komt, AFAIK) en dan eerst controleren of de wijziging gebeurde in een cel die relevant is voor je. Al doet die For Each () dat effectief al, maar als er maar 1 cel is verandert dat is meer dan die ene regel checken niet zinvol.

Detail: 'If Len(c.Value) > 0 And c.Value = 1', kan je een voorbeeld geven waar Len() <= 0 is maar de Value toch 1? Je kunt dat eerste deel weglaten.

Alternatief: moet het berekenen wel plaatsvinden voor iedere cel?

[breew]

tegenvragen:

1. snap je wel precies wat de macro doet?
2. Waarom in Worksheet_Calculate? Waarom niet in een handmatig gestarte macro?

Mogelijk antwoord op vraag 1
voeg

Application.ScreenUpdating = False

en

Application.ScreenUpdating = True

eens toe. helpt dat?

Daarnaast: je loopt nu (mogelijk) door alle cellen in kolom A (dat zijn er 1.048.576). Het lijkt me sterk dat dat nodig is. selecteer je intersect/usedRange eens... hoe groot is die?
Mijn best practice: Stel het bereik van kolom A vast, door de onderste rij met data te zoeken, en daarmee een range in te stellen
voorbeeld:

Visual Basic:

Set ws = ActiveWorkbook.Sheets("Test")
With ws
  lonlaatsterij = .Cells(Rows.Count, "A").End(xlUp).Row
  Set rng = .Range("A2:A" & lonlaatsterij)
End With

[ Voor 68% gewijzigd door breew op 09-02-2018 08:56 ]

[m-vw]

Aanvulling op @breew

Zet je autofilter aan, dan kan je het volgende gebruiken:

code:

    ActiveSheet.Range("A1:A" & lonlaatsterij).AutoFilter Field:=1, Criteria1:="0"

Dit verbergt in één keer alle 1-en. Ben je dus ook de foutmelding door de intersect kwijt.

Range bepaling kan ook door :

code:

Range("A1").select
Set rng = ActiveSheet.Range(ActiveCell.Offset(1, 0), Selection.End(xlDown))

[ Voor 20% gewijzigd door m-vw op 09-02-2018 09:05 ]

[Verwijderd]

Dank allen voor jullie snelle reacties. Om even jullie vragen te beantwoorden:

- Nee, niet de hele kolom hoeft doorlopen te worden. Enkel cellen A1:A150.
- De Screenupdating regels van @breew werken goed. Met jullie overige antwoorden ben ik nu aan het puzzelen. Ik weet niet precies waar ik welke regels moet plaatsen (?)
- Ik heb dus inderdaad geen ervaring met Macro's en weet niet precies wat deze macro doet, hoewel ik het grotendeels wel kan afleiden. Maar om jullie een beeld te geven: deze heb ik rechtstreeks van internet geplukt en alleen het kolomnummer verandert.
- @breew Het verbergen en verschijnen van de rijen moet automatisch gebeuren, niet handmatig.
- @F_J_K Over het Len() ding heb je inderdaad gelijk. Het is altijd óf 1 óf 0.

[breew]

Verwijderd schreef op vrijdag 9 februari 2018 @ 09:21:
- @breew Het verbergen en verschijnen van de rijen moet automatisch gebeuren, niet handmatig.

Dat begrijp ik. Maar de trigger WANNEER dit automatisch moet gebeuren, heb je in de hand.
Nu is het bij elke herberekening (=vaak!!)

Je kun de code ook triggeren bij elke keer dat een werkblad wordt geactiveerd, of bij elke wijziging, of... of...
https://msdn.microsoft.co...cel.worksheet_events.aspx

En waarom zou je je beperken tot worksheet-events ;-)
MSDN: Workbook Events (Microsoft.Office.Tools.Excel)

[ Voor 16% gewijzigd door breew op 09-02-2018 09:46 ]

[m-vw]

Zoiets:

code:

Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
Application.EnableEvents = False
ActiveSheet.Range("A1:A150").AutoFilter Field:=1, Criteria1:="0"
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Maar dan heb je eigenlijk beide application... statemants niet meer nodig omdat het maar één actie is.
Ik ga er nu wel van uit dat de range altijd A1:A150 is.

[Verwijderd]

m-vw schreef op vrijdag 9 februari 2018 @ 09:49:
Zoiets:

code:

Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
Application.EnableEvents = False
ActiveSheet.Range("A1:A150").AutoFilter Field:=1, Criteria1:="0"
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Maar dan heb je eigenlijk beide application... statemants niet meer nodig omdat het maar één actie is.
Ik ga er nu wel van uit dat de range altijd A1:A150 is.

Dit werkt niet. Zodra ik iets in het blad aanpas zegt ie 0 van de 115 records zijn gevonden en verbergt alles.

[Verwijderd]

Ik heb nu dit staan:

code:

Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
Dim c As Range
Application.EnableEvents = False
For Each c In Intersect(Columns(1), ActiveSheet.UsedRange)
If c.Value = 1 Then
    c.EntireRow.Hidden = True
Else
    c.EntireRow.Hidden = False
End If
Next
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Het tweede probleem is vooralsnog niet verholpen:
Zodra ik naast dit Excel bestand nog een ander Excel bestand open, krijg ik de volgende foutmelding van Microsoft Visual Basic:
Fout 1004 tijdens uitvoering:
Methode Intersect van object_Global is mislukt

Iemand nog tips?

[breew]

Verwijderd schreef op vrijdag 9 februari 2018 @ 10:58:
Ik heb nu dit staan:

code:

Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
Dim c As Range
Application.EnableEvents = False
For Each c In Intersect(Columns(1), ActiveSheet.UsedRange)
If c.Value = 1 Then
    c.EntireRow.Hidden = True
Else
    c.EntireRow.Hidden = False
End If
Next
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Het tweede probleem is vooralsnog niet verholpen:
Zodra ik naast dit Excel bestand nog een ander Excel bestand open, krijg ik de volgende foutmelding van Microsoft Visual Basic:
Fout 1004 tijdens uitvoering:
Methode Intersect van object_Global is mislukt

Iemand nog tips?

Ja.. je moet ActiveSheet eigenlijk nooit gebruiken... Maak een 'harde' referentie naar de sheet waar het om gaat.
Waarom slaat de macro trouwens aan zodra je een nieuw werkboek opent? Ik neem toch aan dat je de code alleen laat triggeren binnen het werkblad (=sheet) waar hij voor bedoeld is?

[m-vw]

Bij mij werkt het wel, maar ik onderzoek het even.,

Je moet sowieso van die intersect afstappen.
Als ik het goed lees draait het altijd om A1:A150, dan hoef je niet dynamisch te doen.

[m-vw]

Ik heb een werkend voorbeeld: https://ufile.io/5u5h2

Is een heel simpel testje.

Chip dubbel...

[ Voor 8% gewijzigd door m-vw op 09-02-2018 11:14 ]

[Verwijderd]

m-vw schreef op vrijdag 9 februari 2018 @ 11:13:
Ik heb een werkend voorbeeld: https://ufile.io/5u5h2

Is een heel simpel testje.

Chip dubbel...

Die werkt inderdaad goed. Wellicht heb ik niet helemaal goed vermeld wat de formules in mijn kolom A doen. Ze geven namelijk óf een 1 terug óf "" dus een lege cel, niet daadwerkelijk een 0. Heeft het hier misschien iets mee te maken? Zo ja, excuses voor foutieve info.

[breew]

Verwijderd schreef op vrijdag 9 februari 2018 @ 11:20:
[...]
Die werkt inderdaad goed. Wellicht heb ik niet helemaal goed vermeld wat de formules in mijn kolom A doen. Ze geven namelijk óf een 1 terug óf "" dus een lege cel, niet daadwerkelijk een 0. Heeft het hier misschien iets mee te maken? Zo ja, excuses voor foutieve info.

Als je een conditie bouwt op 0, en de formules geven geen 0 terug.. tja...
Het kàn werken, maar is niet gegarandeerd om de resultaten op te leveren die je verwacht.

[m-vw]

@Verwijderd Dan gebruik je dit Criteria1:="<>1" ipv Criteria1:="0"

[Verwijderd]

@m-vw Nu krijg ik fout 9 tijdens uitvoering: het subscript valt buiten het bereik. Daarnaast werkt de autofilter niet als ik het blad beveilig (ook al geef ik aan dat autofilter is toegestaan).

@breew Ik begrijp wat je zegt over ActiveSheet. En ik hoef de code inderdaad alleen te triggeren binnen het werkblad. Maar ik weet niet wat het dan wel moet zijn. Heb je een alternatieve code voor me?

[breew]

Verwijderd schreef op vrijdag 9 februari 2018 @ 11:48:
@breew Ik begrijp wat je zegt over ActiveSheet. En ik hoef de code inderdaad alleen te triggeren binnen het werkblad. Maar ik weet niet wat het dan wel moet zijn. Heb je een alternatieve code voor me?

Waar heb je de code nu staan?
Een printscreen van je VBA-editor zou helpen

Als de code op de juiste plek staat (in het code-deel van het werkblad waar het moet draaien), zou je

Visual Basic:

For Each c In Intersect(Columns(1), ActiveSheet.UsedRange)

kunnen vervangen door

Visual Basic:

For Each c In Intersect(Columns(1), UsedRange)

De "ActiveSheet" is dan niet nodig, aangezien de code al in een worksheet-event staat. Alle verwijzingen naar Ranges, etc... zijn dat automagisch naar dat werkblad (tenzij je 'hard' een verwijzing maakt naar een ander werkblad.


proof-of-concept

Visual Basic:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  MsgBox (UsedRange.Address)
End Sub

[ Voor 7% gewijzigd door breew op 09-02-2018 12:21 ]

[Verwijderd]

De "ActiveSheet" is dan niet nodig, aangezien de code al in een worksheet-event staat. Alle verwijzingen naar Ranges, etc... zijn dat automagisch naar dat werkblad (tenzij je 'hard' een verwijzing maakt naar een ander werkblad.

Ik heb de code in Blad1 staan. Alles werkt nu prima, dankjewel! De enige vraag die ik nog heb is hoe ik nou precies de range verander naar A1:A150 in plaats van heel kolom A. Nu heb ik dit nog staan:

code:

Dim c As Range
For Each c In Intersect(Columns(1), UsedRange)
If c.Value = 1 Then
    c.EntireRow.Hidden = True
Else
    c.EntireRow.Hidden = False
End If
Next

[breew]

Verwijderd schreef op vrijdag 9 februari 2018 @ 13:26:
[...]


Ik heb de code in Blad1 staan. Alles werkt nu prima, dankjewel! De enige vraag die ik nog heb is hoe ik nou precies de range verander naar A1:A150 in plaats van heel kolom A.

breew in "[Excel] Voorwaardelijk verbergen van rijen met macro"

[Verwijderd]

breew schreef op vrijdag 9 februari 2018 @ 14:48:
[...]

breew in "[Excel] Voorwaardelijk verbergen van rijen met macro"

Moet ik de code nog verder aanpassen als ik dit toevoeg? Ik weet niet zeker of hij de juiste range nu doorzoekt. Ik heb namelijk niet het idee dat de macro nu sneller is geworden.

code:

Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
Application.EnableEvents = False
Set ws = ActiveWorkbook.Sheets("Blad1")
With ws
  lonlaatsterij = .Cells(Rows.Count, "A").End(xlUp).Row
  Set Rng = .Range("A2:A" & lonlaatsterij)
End With
Dim c As Range
For Each c In Intersect(Rng, UsedRange)
If c.Value = 1 Then
    c.EntireRow.Hidden = True
Else
    c.EntireRow.Hidden = False
End If
Next
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

[breew]

Waarom gebruik je die Intersect met UsedRange nog?

For Each c in Rng

moet gewoon werken, en kan mogelijk schelen.

Daarnaast werk je nog steeds in Worksheet_Calculate. Wat is daar de gedachte achter?

[Verwijderd]

breew schreef op woensdag 14 februari 2018 @ 11:55:
Waarom gebruik je die Intersect met UsedRange nog?

For Each c in Rng

moet gewoon werken, en kan mogelijk schelen.

Daarnaast werk je nog steeds in Worksheet_Calculate. Wat is daar de gedachte achter?

In Worksheet_Calculate werkt hij nu. Ik heb een andere geprobeerd, maar hier werkt de macro niet.

code:

Private Sub Worksheet_Change(ByVal Target As Range)

Ik heb er maar weinig verstand van, moet ik hier Target definiëren ofzo? Of waar ligt het aan?

[breew]

Verwijderd schreef op woensdag 14 februari 2018 @ 12:56:
In Worksheet_Calculate werkt hij nu. Ik heb een andere geprobeerd, maar hier werkt de macro niet.

code:

Private Sub Worksheet_Change(ByVal Target As Range)

Ik heb er maar weinig verstand van, moet ik hier Target definiëren ofzo? Of waar ligt het aan?

Google is je vriendje... ;-)
Worksheet_Change slaat aan op het moment dat je een cel veandert. Deze cel wordt aan de procedure meegegeven als Range "Target"... die hoef je verder in jde code niet te gebruiken of definiëren.

Oftewel: zodra je een cel wijzigt, hoort de code die in Worksheet_Change staat aan te slaan...

[Verwijderd]

@breew In kolom A heb ik ALS formules staan. Deze hebben ook verwijzingen naar Blad 2. Als er op blad 2 iets wijzigt waardoor er op blad 1 een 0 in een 1 verandert, zou de betreffende rij moeten verdwijnen. Maar dit gebeurt pas zodra ik een cel in blad 1 wijzig. Wellicht is Worksheet_Change ook geen juiste optie? Eigenlijk hoeft de macro alleen maar te werken als de ALS formules in kolom A een andere waarde terug geven. Ik hoop dat het een beetje duidelijk is wat ik bedoel haha

[breew]

Verwijderd schreef op woensdag 14 februari 2018 @ 13:52:
@breew In kolom A heb ik ALS formules staan. Deze hebben ook verwijzingen naar Blad 2. Als er op blad 2 iets wijzigt waardoor er op blad 1 een 0 in een 1 verandert, zou de betreffende rij moeten verdwijnen. Maar dit gebeurt pas zodra ik een cel in blad 1 wijzig. Wellicht is Worksheet_Change ook geen juiste optie? Eigenlijk hoeft de macro alleen maar te werken als de ALS formules in kolom A een andere waarde terug geven. Ik hoop dat het een beetje duidelijk is wat ik bedoel haha

dat is nieuwe informatie
Je wilt dus een Worksheet_Change hebben op Blad2, en daarmee wijzigingen doen op Blad1?

Houd er dan rekening mee dat je naar alles in Blad1 expliciet moet verwijzen.
Nu heb je bijvoorbeeld nog die (onnodige?) Intersect erin, waarin je verwijst naar UsedRange. Daarmee verwijs je dus naar de UsedRange van Blad2 (want je zit in het Worksheet_Change event op Blad2).
Rng heb je in je code wel 'hard' gedefineerd als alle gevulde cellen in Kolom A van Blad1...

Rng op Blad1 en UsedRange van Blad2 hebben (uiteraard) nergens een Intersect

Daarom: sloop dat ding eruit, en gebruik de loop die al een aantal keer is voorgesteld.

[Verwijderd]

breew schreef op woensdag 14 februari 2018 @ 14:01:
[...]


dat is nieuwe informatie
Je wilt dus een Worksheet_Change hebben op Blad2, en daarmee wijzigingen doen op Blad1?

Houd er dan rekening mee dat je naar alles in Blad1 expliciet moet verwijzen.
Nu heb je bijvoorbeeld nog die (onnodige?) Intersect erin, waarin je verwijst naar UsedRange. Daarmee verwijs je dus naar de UsedRange van Blad2 (want je zit in het Worksheet_Change event op Blad2).
Rng heb je in je code wel 'hard' gedefineerd als alle gevulde cellen in Kolom A van Blad1...

Rng op Blad1 en UsedRange van Blad2 hebben (uiteraard) nergens een Intersect

Daarom: sloop dat ding eruit, en gebruik de loop die al een aantal keer is voorgesteld.

Die intersect heb ik wel er uit gehaald. Maar het probleem is dat die Worksheet_Change alleen werkt bij handmatige invoer. En dat is dus nooit het geval. Daarom gebruik ik nu maar gewoon Calculate.

Ik loop nu echter tegen een ander probleem aan. Soms, als ik een ander Excel bestand open (los van deze macro) en ik wijzig in dat bestand iets, dan krijg ik:

Fout 91 tijdens uitvoering: Objectvariabele of blokvariabele With is niet ingesteld

in deze regel;

code:

Set ws = ActiveWorkbook.Sheets("Blad1")

En dit krijg ik bij sommige bestanden wel, en bij sommige bestanden niet. De macro moet gewoon alleen werken voor dat ene bestand waarvoor ik het nodig heb. En tegelijkertijd wil ik gewoon andere Excel bestanden kunnen openen en wijzigen zonder foutmeldingen. Wat doe ik mis? Mijn code staat hier:

[breew]

De verwijzing naar ActiveWorkbook is de vermoedelijke booszoener. Die zou je er uit kunnen halen, door te verwijzen naar een bestand+locatie, ipv het actieve bestand.

Visual Basic:

Set wb = Workbooks("Book1.xlsx")
Set ws = wb.sheets("Blad1")

[F_J_K]

Dat ja. Je verwijst naar het actieve workbook. En dat is natuurlijk niet per se jouw document, als je meer dan 1 bestand open hebt staan.

Verwijderd schreef op woensdag 14 februari 2018 @ 12:56:
Ik heb er maar weinig verstand van

Is het niet hoog tijd om eerst een tutorial of twee te lezen? Iets als ActiveWorkbook / -Sheet is namelijk een 'standaardfoutje' dat naar ik hoop in iedere cursus wordt besproken maar veel kan slopen zonder dat je het kunt repareren als je geen backups hebt.

offtopic:
Ik zou ook wat meerzeggende namen dan 'Blad 1' gebruiken, maar dat terzijde.

[Verwijderd]

breew schreef op donderdag 15 februari 2018 @ 11:19:
De verwijzing naar ActiveWorkbook is de vermoedelijke booszoener. Die zou je er uit kunnen halen, door te verwijzen naar een bestand+locatie, ipv het actieve bestand.

Visual Basic:

Set wb = Workbooks("Book1.xlsx")
Set ws = wb.sheets("Blad1")

Nu krijg ik fout 9: het subscript valt buiten het bereik

[breew]

Verwijderd schreef op donderdag 15 februari 2018 @ 11:40:
[...]


Nu krijg ik fout 9: het subscript valt buiten het bereik

Ik neem aan dat je "Book1.xlsx" hebt vervangen door de daadwerkelijke bestandsnaam.. en dat deze geopend is?

Verder wat @F_J_K zegt

[ Voor 11% gewijzigd door breew op 15-02-2018 11:45 ]

[Verwijderd]

breew schreef op donderdag 15 februari 2018 @ 11:44:
[...]

Ik neem aan dat je "Book1.xlsx" hebt vervangen door de daadwerkelijke bestandsnaam.. en dat deze geopend is?

Ja en ja, daarom snap ik het niet

[breew]

Verwijderd schreef op donderdag 15 februari 2018 @ 11:46:
[...]


Ja en ja, daarom snap ik het niet

Tijd voor debuggen dan. Zet een breakpoint op de bovenste regel van de code, en stap daarna door de code en ga na op welke regel hij eruit klapt...
Geen idee waar ik het over heb? --> Google op "excel vba debugging" en lees de eerste 10 hits.

In "Venster lokale variabelen" kan je zien wat de waarden van de verschillende variabelen is. Komen die overeen met wat je verwacht?

[ Voor 10% gewijzigd door breew op 15-02-2018 11:50 ]