rijen kleuren / verborgen rijen overslaan

[cocko]

Ik gebruik onderstaande code om rijen om en om te kleuren. Wie kan mij vertellen wat ik hieraan toe moet voegen om de verbrogen rijen over te slaan?

For x = 2 To Range("a65536").End(xlUp).Row - 1
If x / 2 = Int(x / 2) Then
Range(Cells(x, 1), Cells(x, 12)).Interior.Color = RGB(242, 242, 242)
End If
Next x

Groeten Henk

 

[zapatr]

Bovenstaande code heb ik n.a.v. een vraag onlangs op dit forum geplaatst.
Die aanpassen voor verborgen rijen is erg eenvoudig en als je goed in de helpfile had gezocht, had je ook vast wel zelf een oplossing gevonden denk ik
Wijzig de If-regel als volgt:

If WorksheetFunction.And(Rows(x).Hidden = False, x / 2 = Int(x / 2)) Then

 

[cocko]

Ja, klopt, je had die code voor mij geschreven. Ik ben (nog) niet zo thuis in VBA. Ik probeer het altijd wel zelf, maar als dat niet lukt dan roep ik hier om hulp!

Overigens wil de code die je nu voor mij geschreven hebt niet werken. Als rij 4 verborgen is dan zijn rij 3 en 5 alsnog beide gekleurd.
Mijn bedoeling is dat als rij 4 verborgen is: rij 3 gekleurd, 4 verborgen, 5 niet gekleurd en 6 weer gekleurd is.

 

[zapatr]

Overigens wil de code die je nu voor mij geschreven hebt niet werken.

Je had hierboven gevraagd om de rijen om en om te kleuren. Welnu, dat doet de code ook:
de rijen 2, 4, 6, 8, enz. (de even rijen) worden gekleurd als ze niet verborgen zijn.
Om in plaats van de even de oneven rijen te kleuren is natuurlijk erg eenvoudig.
'

Als rij 4 verborgen is dan zijn rij 3 en 5 alsnog beide gekleurd.

Dat is niet juist. Als die bij jou wel gekleurd zijn, dan heb je wijzigingen in de macro aangebracht.
Test de macro maar eens in een werkblad waarin geen enkele rij gekleurd is, dan zul je zien dat enkel de even rijen gekleurd worden voor zover ze niet verborgen zijn.
'

Mijn bedoeling is dat als rij 4 verborgen is: rij 3 gekleurd, 4 verborgen, 5 niet gekleurd en 6 weer gekleurd is.

Dat is dan een heel andere vraagstelling, waarbij we er niet meer vanuit mogen gaan (zoals in de gegeven macro het geval is), dat alleen de even (of alleen de oneven) rijen gekleurd moeten worden. En dat roept onmiddellijk een andere vraag op: Begonnen wordt met een gekleurde rij 2. En nu schrijf je dat als rij 4 verborgen is, rij 3 gekleurd moet zijn. Dat wil dan zeggen dat zowel rij 2 als rij 3 (dus 2 opeenvolgende rijen) gekleurd zijn. Dat is dan weer in strijd met je eerste vraag. Kortom: ik begrijp het niet meer helemaal. Daarom graag een duidelijke en slechts voor 1 uitleg vatbare omschrijving van de vraag a.u.b.
'

 

[cow18]

onderstaande code kijkt naar de zichtbare A-cellen in de 1e 1000 rijen, gaat die om en om 10 cellen breed aan een 1e en 2e bereik toevoegen. Daarna kleurt die dat 1e en 2e bereik in 1 keer in 2 kleurtjes (veel sneller)

Sub KleurenOmEnOM()
  Dim c As Range, R1 As Range, R2 As Range, b As Boolean, kolommen As Integer
  kolommen = 10                                            'ik wil zoveel kolommen breed kleuren
  For Each c In ActiveSheet.Range("A1:A1000").SpecialCells(xlVisible)  'alle zichtbare cellen in je gewenste bereik (1 kolom)
    If b Then                                              'bitje dat om en om gaat
      Set R1 = Union(IIf(R1 Is Nothing, c, R1), c.Resize(, kolommen))  'toevoegen aan 1e bereik
    Else
      Set R2 = Union(IIf(R2 Is Nothing, c, R2), c.Resize(, 10))  'toevoegen aan 2e bereik
    End If
    b = Not b                                              'was b vorige keer True, dan nu False en omgekeerd
  Next
  R1.Interior.ColorIndex = 3                               '1e bereik kleur je zo
  R2.Interior.ColorIndex = 4                               '2e bereik kleur je zo
End Sub

 

[cow18]

verbeterde versie, hij gaat blijkbaar gigantisch gaan vertragen als het aantal bereiken in die Union te groot wordt ...

Option Explicit

Public R1      As Range
Public R2      As Range

Sub KleurenOmEnOM()
  Dim c As Range, b As Boolean, kolommen As Integer
  kolommen = 10                                            'ik wil zoveel kolommen breed kleuren
  For Each c In ActiveSheet.Range("A1:A17000").SpecialCells(xlVisible)  'alle zichtbare cellen in je gewenste bereik (1 kolom)
    If b Then                                              'bitje dat om en om gaat
      Set R1 = Union(IIf(R1 Is Nothing, c, R1), c.Resize(, kolommen))  'toevoegen aan 1e bereik
    Else
      Set R2 = Union(IIf(R2 Is Nothing, c, R2), c.Resize(, 10))  'toevoegen aan 2e bereik
    End If
    b = Not b                                              'was b vorige keer True, dan nu False en omgekeerd
    If (c.Row Mod 100) = 0 Then
      Application.StatusBar = "rij " & c.Row
      If R1.Areas.Count > 250 Then Kleuren
    End If
  Next
  Kleuren
  Application.StatusBar = ""
End Sub

Sub Kleuren()
  R1.Interior.ColorIndex = 3                               '1e bereik kleur je zo
  R2.Interior.ColorIndex = 4                               '2e bereik kleur je zo
  Set R1 = Nothing
  Set R2 = Nothing
End Sub

 

[cocko]

@ Zapatr

Excuses, ik heb niet exact opgelet of de even of de oneven rijen gekleurd worden. Je hebt gelijk. Echter welk doel zou ik kunnen hebben om een rij die toch verborgen is al dan niet te kleuren?
Mijn bedoeling: van rij 2 tot de laatste rij -1 de zichtbare rijen om en om kleuren. (puur voor de leesbaarheid van het geheel, zodat je wat makkelijker op een print de rij kan volgen!, De kleuren worden dan ook niet rood en groen ofzo, maar bijvoorbeeld wit en grijs)

Rij 2 zichtbaar: grijs
Rij 3-6 verborgen, Rij 7 zichtbaar : wit
Rij 8 verborgen, Rij 9: grijs
Rij 10-11 verborgen, Rij 12: wit

(en de kleuren mogen ook andersom, dat maakt uiteraard niets uit!)

@ cow18

Jouw code werkt mooi, alleen hoe krijg ik het bereik zo dat hij bij de op één na laatste ingevulde rij stopt?

 

[cow18]

De laatste en voorlaatste rij gaat hij niet meer aanpassen met onderstaande verandering.

Lastrij = ActiveSheet.Range("A" & Rows.Count).End(xlUp) 'laatste rij
For Each c In ActiveSheet.Range("A1:A" & Lastrij - 2).SpecialCells(xlVisible) 'alle zichtbare cellen in je gewenste bereik (1 kolom)

 

[zapatr]

Mijn bedoeling: van rij 2 tot de laatste rij -1 de zichtbare rijen om en om kleuren. (puur voor de leesbaarheid van het geheel, zodat je wat makkelijker op een print de rij kan volgen!, De kleuren worden dan ook niet rood en groen ofzo, maar bijvoorbeeld wit en grijs)
Rij 2 zichtbaar: grijs
Rij 3-6 verborgen, Rij 7 zichtbaar : wit
Rij 8 verborgen, Rij 9: grijs
Rij 10-11 verborgen, Rij 12: wit

Probeer onderstaande code, maar let er daarbij op dat ik die van toepassing laat zijn op blad1: Sheets(1).
Moet ze bij jou in een ander blad werken, wijzig dan die 1 tussen haakjes in een ander nummer.

Sub macro1()
With Sheets(1)
Dim a As Integer, lr As Integer
lr = [a65536].End(xlUp).Row - 1
a = 0
.Range(.Cells(1, 1), .Cells(lr, 12)).Interior.ColorIndex = xlNone
For x = 1 To lr
If .Rows(x).Hidden = False Then
a = a + 1
If a / 2 = Int(a / 2) Then
.Range(.Cells(x, 1), .Cells(x, 12)).Interior.ColorIndex = 15
End If
End If
Next x
End With
End Sub

 

[cow18]

@Zapatr
onderstaande code maakt van een boolean variabele b een flipflop (afwisselend False/True).
De grootste fout zit hem wel dat je iedere rij opnieuw moet kleuren en niet enkel de even zichtbare rijen, want een oneven zichtbare rij die de vorige keer gekleurd werd, blijft gekleurd ...

If .Rows(x).Hidden = False Then                            'is je rij niet verborgen ?
  b = Not b                                                'b is een boolean, dus indien die false is, dan is die nu True en omgekeerd
  .Range(.Cells(x, 1), .Cells(x, 12)).Interior.ColorIndex = IIf(b, 15, 3)  'of kleurtje 15 of kleurtje 3
End If

 

[cocko]

@Cow18

Ik krijg foutmelding:

Een variabele niet gedefinieerd.

waarschijnlijk moet er nog bij:

Dim Lastrij as...

Alleen As wat? Ik dacht zelf Integer, maar dan krijg ik wederom een foutmelding: typen komen niet overeen.

 

[zapatr]

cow18,
Afgezien van het feit dat ik iets over het hoofd zie, denk ik dat mijn code hierboven juist is.
Je opmerking dat mijn macro de even zichtbare rijen kleurt, is namelijk niet juist.
Alle zichtbare rijen worden om en om gekleurd, ongeacht of het even- of oneven rijen zijn.
En dat is toch wat de vragensteller wenst ?

 

[Warme bakkertje]

@ zapatr
Kleine kanttekening

With Sheets(1)

Het gevaarlijke hiervan is dat wanneer je met je werkbladen gaat schuiven of extra werkbladen gaat invoegen het hopeloos mis kan lopen op een verkeerd werkblad. Wil je absolute zekerheid dat de code op het juiste blad draait, gebruik dan de bladnaam(tussen haakjes) of beter nog de codenaam v/h betreffende blad (niet tussen haakjes)
2. In je code .(punt) vergeten voor [A65536] bij definieëren van lr

 

[cocko]

@ Zapatr

Jouw code werkt voor mij in elk geval prima. Hartelijk bedankt!

@ warme bakkertje

Ik heb jouw kanttekening overgenomen! Dank U!

 

[cow18]

@ Cocko,
boven de 32000 en een beetje heb je geen integer meer, maar een long !

@Zapatr, lees mijn opmerking over je fout nog een keer en geef me dan gelijk ...:thumb:
sorry, Zapatr, jij hebt gelijk, het gaat wel goed en bovendien is je code ruim 2* sneller dan de mijne
Alleen zijn a, x en lr long-variabelen, geen integers voorbij de 32.000

 

[cocko]

@ cow18

Weer wat geleerd! Hartelijk dank voor de hulp!

 

[zapatr]

boven de 32000 en een beetje heb je geen integer meer, maar een long !

Dat is bekend, ook bij de vragensteller denk ik.

@Zapatr, lees mijn opmerking over je fout nog een keer en geef me dan gelijk ...:thumb:

Ik neem aan dat je doelt op deze opmerking van je? :

De grootste fout zit hem wel dat je iedere rij opnieuw moet kleuren en niet enkel de even zichtbare rijen, want een oneven zichtbare rij die de vorige keer gekleurd werd, blijft gekleurd ...

Nogmaals: ik kan me vergissen, maar daar kan ik het met de beste wil van de wereld niet mee eens zijn.
Voer het voorbeeld van Cocko uit, dus verberg de rijen 3, 4, 5, 6, 8, 10, en 11.
Run dan mijn macro.
Dan zul je zien dat de zichtbare rijen om en om gekleurd worden, o.a. ook rij 9, en vanaf rij 13 alle oneven rijen (of de rijnummers die gekleurd worden even of oneven zijn, hangt af van het feit welke rijnummers verborgen zijn).
Breng nu een wijziging aan in de verborgen rijen (verberg meer of minder rijen) en run de macro opnieuw.
Dan zul je zien dat een oneven rij die voor de wijziging gekleurd werd, niet noodzakelijk gekleurd blijft (dat dat wel zo is, is jouw stelling).

Wat nadere uitleg:
- Je hebt enerzijds de rijnummers in Excel, die onveranderd blijven;
- Je hebt anderzijds de rijen (ongeacht welke rijnummers dat in Excel zijn) die wel (of niet) gekleurd moeten worden. Om dat laatste te bepalen, heb ik de variabele a ingevoerd, die telt de rijen die zichtbaar zijn.
- Die zichtbare rijen moeten om en om gekleurd worden. Welnu: telkens als a even is (dus NIET noodzakelijk als het RIJnummer in Excel even is) wordt de rij gekleurd.
Er wordt in principe gestart met een range waarin geen enkele rij gekleurd is, nl:
.Range(.Cells(1, 1), .Cells(lr, 12)).Interior.ColorIndex = xlNone
Bij die coderegel is een opmerking op zijn plaats. De vragensteller moet bedenken wat ongeveer het maximale aantal rijen is dat hij denkt te gaan gebruiken. Als na een wijziging van gegevens de macro wordt uitgevoerd en het aantal rijen met gegevens is kleiner dan voor de wijziging, dan worden met die ene coderegel hierboven niet alle rijen ontdaan van hun kleur (nl. de onderste rijen niet). Om er zeker van te zijn dat alle rijen ontkleurd worden, kan die ene regel daarom beter vervangen worden door:
- ofwel: .Range(.Cells(1, 1), .Cells(3000, 12)).Interior.ColorIndex = xlNone (als bekend is dat het aantal rijen met gegevens nooit groter zal zijn dan 3000; het getal 3000 moet de vragensteller naar eigen inzicht aanpassen);
- ofwel: .cells.interior.colorindex = xlnone (waarmee van alle cellen in het gehele werkblad de opvulkleur wordt verwijderd).
Hopelijk is dit duidelijk.