Gennemløb af datasæt

Beskrivelse

Gennemløb af datasæt

Der er flere metoder til gennemløb af datasæt. Jeg kan bedst lide denne, men den er måske lidt svær at forstå

Dim c As Range  For Each c in Range("A1",Range("A1").End(xlDown)).Cells   ... Skriv den kode der skal køre på den enkelte linje  Next

Første gang koden mellem For og Next kører vil c pege på A1, anden gang peger c på A2 osv. Man kan så f.eks. skrive

Range(c, c.End(xlToRight)).Select

En anden mulighed er denne:

Range("A1").Select Do Until IsEmpty(ActiveCell)   ... den kode der skal køre på den enkelte linje ...    ActiveCell.Offset(1,0).Select Loop

Her dukker et enkelt nyt objekt op, men det er nok ikke den store overraskelse, at ActiveCell peger på den celle der er markeret i arket. Denne metode flytter den aktive celle ned gennem datasættet.

Et lille eksempel

Lad os forestille os, at vi har et regneark, der ser ca. sådan her ud:

	A	B	C
1	Varenummer	Varetekst	Pris
2	12211	Stor vandhane	12,21
3	12213	Mellem vandhane	10,12
4	12215	Lille vandhane	7,94
5	12219	Pakning	4,10

En snut kode, der skriver "OK" ud for alle varene kunne se sådan her ud:

Dim c as Range For Each c In Range("C2", Range("C2").End(xlDown)).Cells   c.Offset(0,1).Value = "OK" Next

Det indre i løkken "c.Font.Bold = True" bliver løbet igennem 4 gange og hver gang peger variablen på en celle i kolonne C - først C2, dernæst C3, C4 og C5 (som valgt i Range("C2", Range("C2").End(xlDown)).Cells).

Det betyder også, at hvis vi vil gøre noget ved hele rækken i stedet for kun kolonne C kunne det se sådan her ud:

Dim c as Range For Each c in Range("C2", Range("C2").End(xlDown)).Cells   Range(c, c.End(xlToLeft)).Font.Bold = True Next

Og hvis vi nu kun vil gøre noget ved de priser, der er over 10 kr ser det sådan her ud:

Dim c as Range For Each c in Range("C2", Range("C2").End(xlDown)).Cells   If c.Value > 10 Then     Range(c, c.End(xlToLeft)).Font.Bold = True   End If Next

Og endelig, hvis vi kun vil gøre noget ved varenumre større end 12213 og pris under 10 kr:

Dim c as Range For Each c in Range("C2", Range("C2").End(xlDown)).Cells   If c.Value < 10 and c.Offset(0,-2).Value > 12213 Then     Range(c, c.End(xlToLeft)).Font.Bold = True   End If Next

Slet rækker

Et par eksempler på, hvordan man kan slette rækker i et datasæt

Sub SletRækker1()   Dim i As Long   Application.ScreenUpdating = False   For i = Cells(Rows.Count, "C"). End(xlUp).Row To 1 Step -1     If Cells(i, "C").Value = "Mango" Then Cells(i, "C").EntireRow.Delete   Next End Sub

Det er én måde at gøre det på - en noget hurtigere måde udnytter Excel's find funktion

Sub SletRækker2()   Dim rgFundetCelle As Range   Application.ScreenUpdating = False   Set rgFundetCelle = Range("C:C").Find(what:="Mango")   Do Until rgFundetCelle Is Nothing     rgFundetCelle.EntireRow.Delete     Set rgFundetCelle = Range("C:C").FindNext   Loop End Sub

Den absolut hurtigste metode til at slette rækker er at bruge Excel's autofilter:

Sub SletRækker3()   Dim lSidsteRække As Long   Dim rng As Range   Application.ScreenUpdating = False   Rows(1).Insert                      'Indsæt en tom række i toppen til feltnavn (behøves ikke hvis der allerede er feltnavne)   Range("C1").Value = "Temp"   With ActiveSheet     .UsedRange                        'Nulstil LastCell     lSidsteRække = .Cells.SpecialCells(xlCellTypeLastCell).Row     Set rng = Range("C1", Cells(lSidsteRække, "C"))     rng.AutoFilter Field:=1, Criteria1:="Mango"     rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete     .UsedRange   End With End Sub

Slet kolonner

Vil man gerne slette tomme kolonner kan det se sådan her ud

Sub FjernTommeKolonner()     Dim i As Integer     For i = Range("A1").End(xlToRight).Column To 1 Step -1         If Cells(1, i).End(xlDown).Row = ActiveSheet.Rows.Count Then             Cells(1, i).EntireColumn.Delete         End If     Next End Sub

Her har jeg antaget at data starter i A1 og at der er overskrifter (uden huller) i første række.

En lille film...

Øvelse

Download dette regnark og skriv makroer så knapperne virker efter hensigten. En post godkendes ved at der står "JA" i Godkendt kolonnen - ellers skal der stå "Nej".

Husk at din makro skal være uafhængig af, hvor mange poster der er i datasættet.

Send regnearket til mig.

Løsning

Se mit løsningsforslag her.

Se min kode her:

Sub GodkendAlle()     Dim c As Range     Dim sidsteR As Long     sidsteR = ActiveSheet.Rows.Count     For Each c In Range("J2", Range("I" & sidsteR).End(xlUp).Offset(0, 1))         c.Value = "JA"     Next End Sub

Sub GodkendOver2000()     Dim c As Range     Dim sidsteR As Long     sidsteR = ActiveSheet.Rows.Count     For Each c In Range("J2", Range("I" & sidsteR).End(xlUp).Offset(0, 1))         If c.Offset(0, -2).Value >= 2000 Then             c.Value = "JA"         Else             c.Value = "Nej"         End If     Next End Sub

Sub GodkendPiaTilPetersData()     Dim c As Range     Dim sidsteR As Long     sidsteR = ActiveSheet.Rows.Count     For Each c In Range("J2", Range("I" & sidsteR).End(xlUp).Offset(0, 1))         If c.Offset(0, -5).Value = "Pia Petersen" And c.Offset(0, -6) = "Peters Data" Then             c.Value = "JA"         Else             c.Value = "Nej"         End If     Next End Sub

Forrige Markering af område

Næste Formatering

Brug for hjælp til VBA, VSTO eller SQL?

Scient Data tilbyder professionel IT-konsulentbistand

Kontakt Scient Data →