Den här artikeln kommer att visa hur du skapar ett dynamiskt intervall i Excel VBA.
Att deklarera ett specifikt cellintervall som en variabel i Excel VBA begränsar oss till att endast arbeta med de specifika cellerna. Genom att deklarera dynamiska intervall i Excel får vi mycket mer flexibilitet över vår kod och den funktionalitet som den kan utföra.
Refererar till intervall och celler
När vi refererar till Range- eller Cell -objektet i Excel hänvisar vi normalt till dem genom hårdkodning i den rad och kolumner som vi behöver.
Range Fastighet
Med hjälp av intervallegenskapen, i exemplets kodrader nedan, kan vi utföra åtgärder på detta område, till exempel att ändra färg på cellerna eller göra cellerna djärva.
| 12 | Område ("A1: A5"). Font.Color = vbRedOmråde ("A1: A5"). Font.Bold = True |
Cells egendom
På samma sätt kan vi använda egenskapen Celler för att referera till ett cellintervall genom att direkt referera till raden och kolumnen i cellegenskapen. Raden måste alltid vara en siffra men kolumnen kan vara ett tal eller en bokstav med citattecken.
Till exempel kan celladressen A1 refereras till som:
| 1 | Celler (1,1) |
Eller
| 1 | Celler (1, "A") |
För att använda Cells -egenskapen för att referera till ett cellintervall måste vi ange början av intervallet och slutet av intervallet.
Till exempel för referensintervall A1: A6 kan vi använda denna syntax nedan:
| 1 | Område (celler (1,1), celler (1,6) |
Vi kan sedan använda egenskapen Cells för att utföra åtgärder på intervallet enligt exemplets kodrader nedan:
| 12 | Område (celler (2, 2), celler (6, 2)). Font.Color = vbRedRange (Cells (2, 2), Cells (6, 2)). Font.Bold = True |
Dynamiska intervall med variabler
Eftersom storleken på våra data ändras i Excel (det vill säga att vi använder fler rader och kolumner än de områden som vi har kodat), skulle det vara användbart om de områden som vi hänvisar till i vår kod också skulle förändras. Med hjälp av intervallobjektet ovan kan vi skapa variabler för att lagra det maximala rad- och kolumnnumret för området i Excel -kalkylbladet som vi använder och använda dessa variabler för att dynamiskt justera intervallobjektet medan koden körs.
Till exempel
| 1234 | Dim lRad som heltalDim lCol som heltallRow = Range ("A1048576"). Slut (xlUp) .RowlCol = Range ("XFD1"). Slut (xlToLeft) .Column |
Sista raden i kolumnen
Eftersom det finns 1048576 rader i ett kalkylblad, kommer variabeln lRow att gå längst ner på arket och sedan använda den speciella kombinationen av Avsluta -tangenten plus uppåtpilen för att gå till den sista raden som användes i kalkylbladet - detta ger oss numret på raden som vi behöver i vårt sortiment.
Sista kolumnen i rad
På samma sätt kommer lCol att flytta till kolumn XFD som är den sista kolumnen i ett kalkylblad och sedan använda den speciella tangentkombinationen Avsluta plus vänsterpil för att gå till den sista kolumnen som används i kalkylbladet - detta ger oss nummer för den kolumn som vi behöver i vårt sortiment.
För att få hela intervallet som används i kalkylbladet kan vi därför köra följande kod:
| 1234567891011 | Sub GetRange ()Dim lRad som heltalDim lCol som heltalDim rng As RangelRow = Range ("A1048576"). Slut (xlUp) .Row'använd lRow för att hitta den sista kolumnen i intervalletlCol = Range ("XFD" & lRow) .End (xlToLeft) .ColumnStäll in rng = Range (Cells (1, 1), Cells (lRow, lCol))'msgbox för att visa oss intervalletMsgBox "Range is" & rng.AddressAvsluta Sub |
Specialceller - LastCell
Vi kan också använda SpecialCells -metoden för intervallobjektet för att få den sista raden och kolumnen som används i ett kalkylblad.
| 123456789101112 | Sub UseSpecialCells ()Dim lRad som heltalDim lCol som heltalDim rng As RangeDim rngBegin As RangeAnge rngBegin = Range ("A1")lRow = rngBegin.SpecialCells (xlCellTypeLastCell) .RowlCol = rngBegin.SpecialCells (xlCellTypeLastCell) .ColumnStäll in rng = Range (Cells (1, 1), Cells (lRow, lCol))'msgbox för att visa oss intervalletMsgBox "Range is" & rng.AddressAvsluta Sub |
UsedRange
Den använda intervallmetoden innehåller alla celler som har värden i det aktuella kalkylbladet.
| 123456 | Sub UsedRangeExample ()Dim rng As RangeAnge rng = ActiveSheet.UsedRange'msgbox för att visa oss intervalletMsgBox "Range is" & rng.AddressAvsluta Sub |
CurrentRegion
Den aktuella regionen skiljer sig från UsedRange genom att den tittar på cellerna som omger en cell som vi har deklarerat som ett startintervall (dvs. variabeln rngBegin i exemplet nedan) och sedan tittar på alla celler som är "bifogade" eller associerade till den deklarerade cellen. Om en tom cell i en rad eller kolumn uppstår, slutar CurrentRegion att leta efter ytterligare celler.
| 12345678 | Sub CurrentRegion ()Dim rng As RangeDim rngBegin As RangeAnge rngBegin = Range ("A1")Ange rng = rngBegin.CurrentRegion'msgbox för att visa oss intervalletMsgBox "Range is" & rng.AddressAvsluta Sub |
Om vi använder den här metoden måste vi se till att alla celler i det område du behöver är anslutna utan tomma rader eller kolumner.
Benämnd Range
Vi kan också referera till namngivna områden i vår kod. Namngivna områden kan vara dynamiska så långt som när data uppdateras eller infogas, kan områdesnamnet ändras så att den innehåller nya data.
I det här exemplet ändras teckensnittet till fetstil för intervallnamnet "Januari"
| 12345 | Sub RangeNameExample ()Dim rng som RangeStäll in rng = Range ("januari")rng.Font.Bold = = SantAvsluta Sub |
Som du kommer att se på bilden nedan, om en rad läggs till i intervallnamnet, uppdateras intervallnamnet automatiskt för att inkludera den raden.
Skulle vi sedan köra exempelkoden igen skulle intervallet som påverkas av koden vara C5: C9 medan det i första hand skulle ha varit C5: C8.
Tabeller
Vi kan referera till tabeller (klicka för mer information om hur du skapar och manipulerar tabeller i VBA) i vår kod. När en tabelldata i Excel uppdateras eller ändras, kommer koden som hänvisar till tabellen att hänvisa till den uppdaterade tabelldatan. Detta är särskilt användbart när det gäller pivottabeller som är anslutna till en extern datakälla.
Med denna tabell i vår kod kan vi hänvisa till tabellens kolumner med rubrikerna i varje kolumn och utföra åtgärder på kolumnen enligt deras namn. När raderna i tabellen ökar eller minskar enligt data, kommer tabellintervallet att anpassas därefter och vår kod fungerar fortfarande för hela kolumnen i tabellen.
Till exempel:
| 123 | Sub DeleteTableColumn ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListColumns ("Supplier"). RaderaAvsluta Sub |
