Excel 2003 automatisch zoeken naar volgende "niet lege cel"

[erka]

uitleg bij de bijlage:

ik heb een werkblad met clubleden (namen) genummerd van 100 t/m 999, niet alle nummers worden gebruikt.
graag wil ik in de bladen 100 en 200 alleen de gevulde cellen laten zien (in dit geval zijn dat 101,103,108,112,116,200,204 en 208)

een getal waarbij geen naam ingevuld is, wil ik niet laten zien. (oftewel de lege cellen weglaten)

kan dit dmv een formule, of is hiervoor een macro nodig?

ik werk met excel 2003

 

[ronald1986]

Met een autofilter waar je filterd op gevulde cellen in de kolom waar de namen staan.

 

[spaarie]

Wil je de makkelijke manier of de moeilijke manier?
Makkelijk = filter op kolom B en de lege cellen eruit filteren
Moeilijk = een matrixformule (die kan je krijgen als je voor de moeilijke weg kiest )

 

[Niels28]

Met een macro,

voeg op tabblad namen wel even kolomnamen in, dus A1=nummer en B1 = Naam.

Sub verdelen()
Dim sh As String
With Sheets("namen").Range("A1:B" & Sheets("namen").Cells(Rows.Count, 1).End(xlUp).Row)
.AutoFilter Field:=2, Criteria1:="<>"
For i = 100 To 200 Step 100
.AutoFilter Field:=1, Criteria1:=">=" & i, Operator:=xlAnd, Criteria2:="<=" & i + 99
sh = i
Sheets("namen").Range("A2:B" & Sheets("namen").Cells(Rows.Count, 1).End(xlUp).Row).Copy Sheets(sh).Range("A1")
Next
.AutoFilter
End With
End Sub

Niels

 

[erka]

Ronald1986, autofilter is een optie, ik moet dan elke keer als er in het blad met namen een nieuwe naam bijkomt er om denken dat ik het autofilter in bladen 100 en 200 opnieuw activeer zodat ze ook hier geactualiseerd worden toch?

Spaarie, de moeilijke manier lijkt mij uiteindelijk uiteindelijk het handigst, hoe zou zo'n matrixformule er uit zien?
(zoals gezegd; bij autofilter moet ik er volgens mij zelf om denken dat het autofilter in de andere werkbladen handmatig moet worden geactualiseerd???)

Niels28, dit lijkt mij de beste oplossing! ik probeer hem alleen te ontcijferen/begrijpen.

stel de werkbladen hebben een andere naam bijvoorbeeld : "leden A" en "leden B" (ipv 100 en 200) en ik wil graag de gegevens vanaf cel A6 plakken ipv A1, is dit mogelijk?

vr. gr. Rutger

 

[spaarie]

Rutger,
Voor in C1

=ALS(RIJ()-1>SOM(--($B$2:$B$31<>""));"";(INDEX(B$2:B$31;KLEINSTE(ALS($B$2:$B$31<>"";RIJ(B$2:B$31)-1);RIJ(B1)))))

LET OP! Matrixformules afsluiten met CTRL+SHIFT+ENTER en niet alleen een ENTER, hij zet zelf deze teken { } erom heen.
Deze kan je vervolgens naar beneden trekken...

Zo moet ie in je Excelbestand eruit zien

{=ALS(RIJ()-1>SOM(--($B$2:$B$31<>""));"";(INDEX(B$2:B$31;KLEINSTE(ALS($B$2:$B$31<>"";RIJ(B$2:B$31)-1);RIJ(B1)))))}

 

[Niels28]

Sub verdelen()

sh = 2 'geef hier aan vanaf welk sheet je wilt beginnen
With Sheets("namen").Range("A1:B" & Sheets("namen").Cells(Rows.Count, 1).End(xlUp).Row)
.AutoFilter Field:=2, Criteria1:="<>"
For i = 100 To 200 Step 100
.AutoFilter Field:=1, Criteria1:=">=" & i, Operator:=xlAnd, Criteria2:="<=" & i + 99
Sheets("namen").Range("A2:B" & Sheets("namen").Cells(Rows.Count, 1).End(xlUp).Row).Copy Sheets(sh).Range("A6")
sh = sh + 1 'sheets worden op volgorde behandeld
Next
.AutoFilter
End With
End Sub

Niels

 

[erka]

Super! nog 1 laatste vraag, ik kan nu de nummers 100 t/m 199 & 200 t/m 299 automatisch kwijt, dit gaat goed en ik begrijp een beetje hoe de macro in elkaar zit.
Als ik een 3e werkblad zou maken waar ik nummers 300 t/m 399 kwijt wil, hoe verwerk ik dit in de macro?

alvast bedankt!

 

[Niels28]

verander de

For i = 100 to 200

in

 for 1 = 100 to 300

Niels

 

[Niels28]

Deze kijkt naar de nummers en voegt automatisch een tabblad in

Sub verdelen()

For Each ws In ThisWorkbook.Sheets
c01 = c01 & "|" & ws.Name & "|"
Next

With Sheets("namen").Range("A1:B" & Sheets("namen").Cells(Rows.Count, 1).End(xlUp).Row)
.AutoFilter Field:=2, Criteria1:="<>"
j = WorksheetFunction.MRound(WorksheetFunction.Max(Range("A:A")), 100)
For i = 100 To j Step 100

x = WorksheetFunction.Choose(i / 100, "Leden A", "Leden B", "Leden C", "Leden D", "Leden E")
If InStr(c01, "|" & x & "|") = 0 Then
With Sheets.Add
.Name = x
.Move after:=Sheets(Sheets.Count)
End With
End If
.AutoFilter Field:=1, Criteria1:=">=" & i, Operator:=xlAnd, Criteria2:="<=" & i + 99
Sheets("namen").Range("A2:B" & Sheets("namen").Cells(Rows.Count, 1).End(xlUp).Row).Copy Sheets(x).Range("A6")
Next
.AutoFilter
End With
End Sub

De namen van de tabbladen zelfs nog aanvullen voor de mogelijkheden of veranderen bij dit gedeelte:

x = WorksheetFunction.Choose(i / 100, "Leden A", "Leden B", "Leden C", "Leden D", "Leden E")

Niels

 

[erka]

Niels, dit is helemaal super!!!

bedankt voor het meedenken!

waar kan ik de taart heen sturen om jou te bedanken?

vriendelijke groet,

Rutger

 

[Niels28]

Klinkt goed maar ik ben al te vreden als je de vraag op opgelost zet.


Niels

 

[erka]

Is geregeld,

bedankt voor de hulp!