Efficiënte Macro in Excel VBA om rijen en kolommen te verbergen

[Robert971]

Hallo,

Ik ben bezig een macro te ontwikkelen voor het afhankelijk verbergen van rijen en kolommen binnen 1 tabblad(sheet).

Het bereik bestaat uit 4816 rijen en kolommen C t/m HD.
Afhankelijk van de waarden in meerdere Textboxen(Active X-besturingselement) op een ander tabblad(sheet) zou ik de rijen en kolommen willen laten verbergen.

De macro is nu als volgt:

Voor het verbergen van Rijen

Private Sub CommandButton2_Click()
If Textbox1.Value >= 1 Then
Sheet2.Range("27:36").EntireRow.Hidden = False
Else
Sheet2.Range("27:36").EntireRow.Hidden = True
End If
If Textbox1.Value >= 2 Then
Sheet2.Range("37:46").EntireRow.Hidden = False
Else
Sheet2.Range("37:46").EntireRow.Hidden = True
End If
If Textbox1.Value >= 3 Then
Sheet2.Range("47:56").EntireRow.Hidden = False
Else
Sheet2.Range("47:56").EntireRow.Hidden = True
End If

Etc. Etc. dit gaat voor textbox1 door tot:

If Textbox1.Value >= 15 Then
Sheet2.Range("167:176").EntireRow.Hidden = False
Else
Sheet2.Range("167:176").EntireRow.Hidden = True
End If

End Sub

Kort samengevat:
Er zijn dus 30 textboxen op sheet 1 die een waarde van 1 t/m 15 kunnen vertegenwoordigen waarna afhankelijk van de ingegeven waarde door de
gebruiker de rijen worden verborgen op sheet 2. Dit tot aan rij 4816.

De volgende problemen onstaan bij deze macro:
- Lange doorlooptijd voordat de macro is uitgevoerd
- Bij het verbergen van eengroot aantal rijen bijv. 4000 loopt excel soms vast (not responding) en is de sheet moeilijk (of niet) te activeren.

Iemand een idee om een efficiëntere macro te schrijven en bovengenoemde problemen op te lossen (het aantal textboxen moet behouden blijven).
Hetzelfde probleem geldt voor de kolommen in deze sheet zie onderstaande macro:

Voor het verbergen van kolommen is er 1 textbox die de waarde 1 t/m 30 kan aannemen:

Private Sub CommandButton2_Click()

If TextboxAantalPartners.Value >= 1 Then
Sheet2.Columns("C:I").EntireColumn.Hidden = False
Else
Sheet2.Columns("C:I").EntireColumn.Hidden = True
End If
If TextboxAantalPartners.Value >= 2 Then
Sheet2.Columns("J:P").EntireColumn.Hidden = False
Else
Sheet2.Columns("J:P").EntireColumn.Hidden = True
End If
If TextboxAantalPartners.Value >= 3 Then
Sheet2.Columns("Q:W").EntireColumn.Hidden = False
Else
Sheet2.Columns("Q:W").EntireColumn.Hidden = True
End If


Etc. Etc. t/m kolom HD

End Sub

Beide macro's werken wel, maar bij het gemeenschappelijk verbergen van grote hoeveelheden rijen en kolommen onstaan problemen,.

Kan iemand mij verder helpen voor bovenstaand?

Alvast dank.

Gr,
Robert

 

[roeljongman]

robert, zet je code even in code tags dan ben je die smilies ook kwijt en een voorbeeldje zonder gevoelige gegevens, om code te testen is ook wel fijn..

zonder voorbeeldje maak ik de analyse dat de textbox een waarde 1 tm 15 oplevert en elke waarde zorgt dat 1 reeks van 10 regels verborgen word en dat maar 1 reeks verborgen is.
dat is prima te vangen in 1 een paar regels code ipv 15 if..then..else lussen

de code is getest met een cel input maar met een textbox.value moet het ook werkbaar zijn.

Private Sub CommandButton2_Click()

firstrow = 17 + textbox1.value * 10
lastrow = 26 + textbox1.value * 10

Blad1.Rows("27:176").Hidden = False
Blad1.Range("A" & firstrow, "A" & lastrow).EntireRow.Hidden = True
end sub

het ligt dus een beetje aan de logica van de andere textboxen of je die ook zo kunt opzetten.

voor kolommen lijkt hetzelfde kunstje te kunnen maar dan met factor 7 (en uiteraard geen letters gebruiken)

firstcol = 3 + TextboxAantalPartners.Value * 7

 

[Robert971]

Efficiënte macro rijen/kolommen verbergen

Roel,

Dank voor je reactie. Ik ben hiermee een stuk geholpen. Een deel van de code heb ik nog wat gewijzigd. Het ging erom dat de reeks na het gekoppelde bereik afhankelijk van de waarde in de textbox wordt verborgen. Voor de maximale waarde die de textbox mag bevatten heb ik nog een "If statement" tussengevoegd. Anders werkte het niet. Zie ook de code in onderstaande tag.

[JS]Private Sub CommandButton1_Click()

'Macro afhankelijk rijen verbergen'

firstrow = 27 + TextBox1.Value * 10
lastrow = 176

Sheet2.Rows("27:176").Hidden = False
Sheet2.Range("A" & firstrow, "A" & lastrow).EntireRow.Hidden = True

If TextBox1.Value = 15 Then
Sheet2.Rows("176:177").Hidden = False
End If

End Sub
[/JS]

Overigens is het ook mogelijk de macro automatisch te laten aanpassen indien er regels (1 of meerdere) worden toegevoegd tussen de rijen en kolommen? (zonder te werken met naam etiketten)

 

[roeljongman]

Overigens is het ook mogelijk de macro automatisch te laten aanpassen indien er regels (1 of meerdere) worden toegevoegd tussen de rijen en kolommen? (zonder te werken met naam etiketten)

nee, dan zou ik toch echt naambereiken gebruiken, excel/vba moet weten waar er is ingevoegd en dat is op basis van rijnummers lastig als je je naam bereiken logisch nummert gekoppeld aan de textbox value hoef je ook geen lange macro te maken.
Dus noem het gehele bereik van rij 27 tm 176 iets als TotaalBereik en de afzonderlijke bereiken RijBereik1 tm Rijbereik15

dan kun je in VBA werken met Range("TotaalBereik").Hidden = False en Range("Bereik" & Textbox.Value).entirerow.hidden = true

hetzelfde bereiken als bovenstaand

 

[Robert971]

Efficiënte macro rijen/kolommen verbergen

O.K., dan ga ik het via die weg proberen.

 

[Robert971]

Macro werkt nog niet

Roel,

De laatst genoemde macro voor het verwijderen van rijen werkt nog niet. Zie ook onderstaande code.

Range("TotaalBereik").Hidden = False 
Range("Bereik" & Textbox.Value).entirerow.hidden = true

Rijbereiken zijn gedefiniëerd. Idem totaalbereik. De laatste regel van de code met 'bereik en textbox.value' wordt in VBA geel gekleurd.
Hoe kan ik dit oplossen? Kun je mogelijk de code anders in zijn geheel uitschrijven, misschien mis ik enkele toevoegingen.

Bvd.

Robert

 

[roeljongman]

Meest waarschijnlijk heb je gewoon ergens een tikfout zitten dit is de kern van de code, als het werkte met gewone rijnummers zou het nu ook moeten werken. zonder voorbeeld is het niet mogelijk voor mij op te zien wat er fout gaat.

 

[zapatr]

Sheet2.rows...

is niet juist.
Dat moet zijn:

Sheets("Sheet2").rows....

als tenminste de naam van het blad Sheet2 is.

 

[roeljongman]

beide is hetzelfde zapatr.. de 2e methode is foutgevoeliger omdat de naam sheets("sheet2") door de gebruiker kan worden gewijzigd in welke willekeurige andere naam. en de naam sheets2 kan alleen in VBA aangepast worden. daarom roep ik liever de vba naam aan dan de sheet naam die door de gebruiker bepaald word

 

[zapatr]

In mijn Excelversie zijn die twee in elk geval niet hetzelfde

Sheet2.rows...

werkt bij mij niet.

 

[roeljongman]

dat zal dan aan jou sheet liggen denk ik.. je kunt gewoon een sheet2 direct aanroepen zonder sheets() object te gebruiken

maar daarnaast.. kan dat niet de fout zijn want die sheet2.rows en sheet2.range(..) regel komt als het goed is niet meer in de macro voor want dat word vervangen door

Range("TotaalBereik").Hidden = False 
Range("Bereik" & Textbox.Value).entirerow.hidden = true

omdat nu met naambereiken gewerkt word..

maar tis niet voor niks dat ik om het bestand vraag.. anders word dit een wedstrijdje "wie het eerste goed gokt".

 

[zapatr]

Roel,
Wat ik hierboven schreef ligt niet aan mijn sheet, dat is in alle Excelprogramma's zo die ik al gezien heb, zowel op mijn computer als op de computers van anderen.
Ik herhaal:

Sheet2.rows....

is niet juist!
En voor het overige maak ik daar geen woorden meer aan vuil.

 

[Robert971]

Effici�nte Macro in Excel VBA om rijen en kolommen te verbergen

Ik ga even wat proberen dan met jullie genoemde info.


Nog een vraag mbt. de code, moet er geen 'firstrow' of 'lastrow' statement worden toegevoegd, in combinatie met Rijbereiken en een waarde voor de textboxvalue?
Stel ik vul textbox 1 met waarde 8, rijbereik 9 t/m 15 moet dan automatisch worden verborgen. Wat voor een gevolgen heeft dit op onderstaande code als deze wordt toegespitst op textbox1: vertegenwoordigd rijbereik 1 t/m 15 ofwel: rijnummer 27 t/m 176 van sheet 2.

Rijbereik 1 begint vanaf rij 27 en eindigt op rijnummer 36 , Rijbereik 2: 37 t/m 46 etc. Rijbereik 15: 167 t/m 176.

De code is nu zoals ik begrijp:

Private Sub Commandbutton1_Click()
Range("TotaalBereik").Hidden = False 
Range("Bereik" & Textbox.Value).entirerow.hidden = true 
End Sub

Hoe komt bovenstaande code er in zijn geheel uit te zien voor textbox 1.

Als het niet lukt na reactie op bovenstaand stuur ik een deel van het bestand mee.

 

[roeljongman]

De code van die 2 regels is in principe voldoende.. het gebruik van die rijnummers wil je juist voorkomen door het gebruik van naambereiken

ik zie net opeens wel een foutje textbox.value moet textbox1.value zijn.. of een ander volgnummer in elk geval moet het de exacte naam van de textbox zijn

hieronder aangepast:

Private Sub Commandbutton1_Click()
Range("TotaalBereik").Hidden = False 
Range("Bereik" & Textbox1.Value).entirerow.hidden = true 
End Sub

 

[Warme bakkertje]

@ Zapatr
Bekijk onderstaande link dan maar eens
http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm

 

[Robert971]

De code zoals onderstaand genoemd werkt nog niet. 'Bereik' heb ik veranderd in Rijbereik. De macro wordt dan als volgt:

Private Sub CommandButton1_Click()
Range("TotaalBereik").EntireRow.Hidden = False
Range("Rijbereik" & TextBox1.Value).EntireRow.Hidden = True
End Sub

Bij gebruik van het woord 'Bereik' in regel 2 van de macro wordt een debug gegeven. Daarom veranderd in 'Rijbereik'. Het probleem wat zich nu voordoet is dat niet de juiste rijen worden verborgen. Het rijbereik van de waarde van de textbox wordt verborgen. Bijvoorbeeld waarde textbox1 = 1, bij uitvoering van macro wordt 'Rijbereik 1' verborgen i.p.v. 'Rijbereik 2 t/m 15' wat oorspronkelijk de bedoeling was.

Een voorbeeld bestandje is toegevoegd om te laten zien wat ik bedoel.

 

[roeljongman]

aha, ja ik had bereik niet letterlijk als naam bedoeld want dat zal een gereserveerde term zijn in excel

het verbergen en tonen van de reeksen dat is simpel op te lossen kwestie van de true en false wisselen in de code..

Private Sub CommandButton1_Click()
Sheet2.Range("TotaalBereik").EntireRow.Hidden = True
Sheet2.Range("Rijbereik" & TextBox1.Value).EntireRow.Hidden = False
End Sub

 

[Robert971]

Macro werkt!

Ja klopt, True en False omwisselen had ik ook geprobeerd. Toen werkte het nog niet. Heb de 'Rijbereiken' moeten aanpassen. Ik had per bereik steeds een bereik van 10 rijen. Dit moet uiteraard met de mogelijke textbox waarde worden vermenigvuldigd.

Oude situatie (fout):
Rijbereik 1 = rij 27 t/m 36
Rijbereik 2 = rij 37 t/m 46

etc.

Nieuwe situatie (goed):
Rijbereik 1 = 27 t/m 36 (is textboxwaarde(=1) * 10)
Rijbereik 2 = 27 t/m 46 (is textboxwaarde(=2) *10)

Nu werkt de macro code prima.

Dank voor je hulp!