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...