Solujen ja alueiden käsittely Excel VBA: ssa (valitse, kopioi, siirrä, muokkaa)

Kun työskentelet Excelin kanssa, suurin osa ajastasi kuluu laskentataulukkoalueelle - solujen ja alueiden käsittelyyn.

Ja jos haluat automatisoida työsi Excelissä VBA: n avulla, sinun on tiedettävä, miten voit työskennellä solujen ja alueiden kanssa VBA: n avulla.

VBA -alueiden avulla voit tehdä monia erilaisia ​​asioita (kuten valita, kopioida, siirtää, muokata jne.).

Joten tämän aiheen kattamiseksi jaan tämän opetusohjelman osiin ja näytän sinulle, kuinka työskennellä solujen ja alueiden kanssa Excel VBA: ssa esimerkkien avulla.

Aloitetaan.

Kaikki tässä opetusohjelmassa mainitsemani koodit on sijoitettava VB -editoriin. Siirry VBA -koodin sijoitusosaan saadaksesi tietää, miten se toimii.

Jos olet kiinnostunut VBA: n oppimisesta helpolla tavalla, tutustu minun Online Excel VBA -koulutus.

Solun / alueen valitseminen Excelissä VBA: n avulla

Jos haluat työskennellä solujen ja alueiden kanssa Excelissä VBA: n avulla, sinun ei tarvitse valita sitä.

Useimmissa tapauksissa sinun on parempi olla valitsematta soluja tai alueita (kuten näemme).

Siitä huolimatta on tärkeää käydä tämä osio läpi ja ymmärtää, miten se toimii. Tämä on ratkaisevaa VBA -oppimisessasi, ja monia tässä käsiteltyjä käsitteitä käytetään tässä opetusohjelmassa.

Aloitetaan siis hyvin yksinkertaisella esimerkillä.

Yksisolun valitseminen VBA: n avulla

Jos haluat valita yhden solun aktiivisesta taulukosta (esimerkiksi A1), voit käyttää alla olevaa koodia:

Sub SelectCell () -alue ("A1"). Valitse End Sub

Yllä olevassa koodissa on pakollinen Sub- ja End Sub -osa sekä koodirivi, joka valitsee solun A1.

Alue ("A1") kertoo VBA: lle sen solun osoitteen, johon haluamme viitata.

Valitse on Range -objektin menetelmä ja valitsee Range -objektissa määritetyt solut/alueen. Soluviittaukset on liitettävä lainausmerkkeihin.

Tämä koodi näyttää virheen, jos kaavioarkki on aktiivinen taulukko. Kaavioarkki sisältää kaavioita, eikä sitä käytetä laajalti. Koska siinä ei ole soluja/alueita, yllä oleva koodi ei voi valita sitä ja näyttää virheen.

Huomaa, että koska haluat valita solun aktiiviselta taulukolta, sinun on vain määritettävä solun osoite.

Mutta jos haluat valita solun toiselta taulukolta (esimerkiksi Sheet2), sinun on ensin aktivoitava Sheet2 ja valittava sitten solu.

Sub SelectCell () -työarkit ("Sheet2"). Aktivoi alue ("A1"). Valitse End Sub

Voit myös aktivoida työkirjan, aktivoida siinä tietyn laskentataulukon ja valita sitten solun.

Sub SelectCell () Työkirjat ("Book2.xlsx"). Laskentataulukot ("Sheet2"). Aktivoi alue ("A1"). Valitse End Sub 

Huomaa, että kun viitataan työkirjoihin, sinun on käytettävä koko nimeä ja tiedostotunnistetta (.xlsx yllä olevassa koodissa). Jos työkirjaa ei ole koskaan tallennettu, sinun ei tarvitse käyttää tiedostopäätettä.

Nämä esimerkit eivät ole kovin hyödyllisiä, mutta näet myöhemmin tässä opetusohjelmassa, kuinka voimme käyttää samoja käsitteitä kopioida ja liittää soluja Excelissä (VBA: n avulla).

Aivan kuten valitsemme solun, voimme myös valita alueen.

Jos kyseessä on alue, se voi olla kiinteä kokoalue tai muuttuva kokoalue.

Kiinteässä kokoluokassa tiedät kuinka suuri alue on ja voit käyttää tarkkaa kokoa VBA -koodissasi. Mutta vaihtelevan kokoisella alueella sinulla ei ole aavistustakaan kuinka suuri alue on ja sinun on käytettävä vähän VBA-taikuutta.

Katsotaanpa, miten tämä tehdään.

Kiinteän kokoisen alueen valitseminen

Tässä on koodi, joka valitsee alueen A1: D20.

Sub SelectRange () -alue ("A1: D20"). Valitse End Sub 

Toinen tapa tehdä tämä on käyttää alla olevaa koodia:

Sub SelectRange () -alue ("A1", "D20"). Valitse End Sub

Yllä oleva koodi ottaa vasemman yläkulman soluosoitteen (A1) ja oikean alakulman soluosoitteen (D20) ja valitsee koko alueen. Tästä tekniikasta on hyötyä, kun työskentelet vaihtelevan kokoisten alueiden kanssa (kuten näemme, kun Lopetus -ominaisuus käsitellään myöhemmin tässä opetusohjelmassa).

Jos haluat valinnan tapahtuvan eri työkirjassa tai eri laskentataulukossa, sinun on kerrottava VBA: lle näiden objektien tarkat nimet.

Esimerkiksi alla oleva koodi valitsee alueen A1: D20 Book2 -työkirjan Sheet2 -laskentataulukossa.

Sub SelectRange () Työkirjat ("Book2.xlsx"). Laskentataulukot ("Sheet1"). Aktivoi alue ("A1: D20"). Valitse End Sub

Entä jos et tiedä kuinka monta riviä siellä on? Entä jos haluat valita kaikki solut, joilla on arvo.

Näissä tapauksissa sinun on käytettävä seuraavassa osassa (muuttuvan kokoisen alueen valitseminen) esitettyjä menetelmiä.

Muuttuvan kokoisen alueen valitseminen

Voit valita useita solualueita eri tavoilla. Valitsemasi menetelmä riippuu tietojen rakenteesta.

Tässä osassa käsittelen joitain hyödyllisiä tekniikoita, jotka ovat todella hyödyllisiä, kun työskentelet alueiden kanssa VBA: ssa.

Valitse CurrentRange -ominaisuuden käyttäminen

Jos et tiedä kuinka monella rivillä/sarakkeella on tietoja, voit käyttää Range -objektin CurrentRange -ominaisuutta.

CurrentRange -ominaisuus kattaa kaikki tietoalueen vierekkäiset täytetyt solut.

Alla on koodi, joka valitsee nykyisen alueen, joka sisältää solun A1.

AlivalintaCurrentRegion () -alue ("A1"). CurrentRegion.Select End Sub

Yllä oleva menetelmä on hyvä, kun sinulla on kaikki tiedot taulukkona ilman tyhjiä rivejä/sarakkeita.

Mutta jos tiedoissasi on tyhjiä rivejä/sarakkeita, se ei valitse tyhjien rivien/sarakkeiden jälkeisiä. Alla olevassa kuvassa CurrentRegion -koodi valitsee tiedot riville 10 asti, koska rivi 11 on tyhjä.

Tällaisissa tapauksissa saatat haluta käyttää laskentataulukko -objektin UsedRange -ominaisuutta.

Valitse UsedRange -ominaisuuden käyttäminen

UsedRangen avulla voit viitata kaikkiin muutettuihin soluihin.

Joten alla oleva koodi valitsee kaikki aktiivisen taulukon käytetyt solut.

Sub SelectUsedRegion () ActiveSheet.UsedRange.Valitse End Sub

Huomaa, että jos sinulla on käytetty kaukana oleva solu, se otetaan huomioon yllä olevalla koodilla ja kaikki solut, kunnes käytetty solu valitaan.

Valitse Pääteominaisuuden käyttäminen

Nyt tämä osa on todella hyödyllinen.

Lopetus -ominaisuuden avulla voit valita viimeksi täytetyn solun. Tämän avulla voit jäljitellä Control -alanuolinäppäimen tai Ylös -nuolinäppäimen tai Oikean/Vasen -näppäimen vaikutusta.

Yritetään ymmärtää tämä esimerkin avulla.

Oletetaan, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat nopeasti valita viimeiset täytetyt solut sarakkeesta A.

Ongelma on tässä, että tiedot voivat muuttua etkä tiedä kuinka monta solua on täytetty. Jos sinun on tehtävä tämä näppäimistöllä, voit valita solun A1 ja käyttää sitten Ctrl + alanuolinäppäintä, jolloin se valitsee viimeisen täytetyn solun sarakkeesta.

Katsotaan nyt, miten tämä tehdään VBA: n avulla. Tämä tekniikka on kätevä, kun haluat siirtyä nopeasti vaihtelevan kokoisen sarakkeen viimeiseen täytettyyn soluun

Sub GoToLastFilledCell () Range ("A1"). End (xlDown) .Valitse End Sub

Yllä oleva koodi hyppää sarakkeen A viimeiseen täytettyyn soluun.

Vastaavasti voit käyttää End (xlToRight) -painiketta hypätäksesi viimeiseen täytettyyn soluun rivissä.

Sub GoToLastFilledCell () Range ("A1"). End (xlToRight) .Valitse End Sub

Entä jos haluat valita koko sarakkeen sen sijaan, että hyppääisit viimeksi täytettyyn soluun.

Voit tehdä sen käyttämällä alla olevaa koodia:

Sub SelectFilledCells () -alue ("A1", alue ("A1"). End (xlDown)). Valitse End Sub

Yllä olevassa koodissa olemme käyttäneet valitun solun ensimmäistä ja viimeistä viittausta. Riippumatta siitä, kuinka monta täytettyä solua on, yllä oleva koodi valitsee kaikki.

Muista yllä oleva esimerkki, jossa valitsimme alueen A1: D20 käyttämällä seuraavaa koodiriviä:

Alue ("A1", "D20")

Tässä A1 oli vasemman yläkulman solu ja D20 oli alueen oikea alakulma. Voimme käyttää samaa logiikkaa valitessamme vaihtelevan kokoisia alueita. Mutta koska emme tiedä oikean alakulman solun tarkkaa osoitetta, saimme sen End-ominaisuudella.

Alueella ("A1", Alue ("A1"). Loppu (xlDown)) "A1" viittaa ensimmäiseen soluun ja alue ("A1"). Loppu (xlDown) viittaa viimeiseen soluun. Koska olemme toimittaneet molemmat viitteet, Select -menetelmä valitsee kaikki solut näiden kahden viitteen väliltä.

Samoin voit myös valita koko tietojoukon, jossa on useita rivejä ja sarakkeita.

Alla oleva koodi valitsee kaikki täytetyt rivit/sarakkeet solusta A1 alkaen.

Sub SelectFilledCells () -alue ("A1", alue ("A1"). End (xlDown) .End (xlToRight)). Valitse End Sub

Yllä olevassa koodissa olemme käyttäneet Range (“A1”). End (xlDown) .End (xlToRight) saadaksesi viittauksen tietojoukon oikeassa alakulmassa olevaan soluun.

Ero CurrentRegionin ja Endin käytön välillä

Jos mietit, miksi valita End -ominaisuus valitaksesi täytetyn alueen, kun meillä on CurrentRegion -ominaisuus, kerron eron.

Loppuominaisuuden avulla voit määrittää aloitussolun. Jos esimerkiksi tiedot ovat muodossa A1: D20, mutta ensimmäinen rivi on otsikoita, voit valita End -ominaisuuden avulla tiedot ilman otsikoita (käyttämällä alla olevaa koodia).

Sub SelectFilledCells () -alue ("A2", alue ("A2"). End (xlDown) .End (xlToRight)). Valitse End Sub

Mutta CurrentRegion valitsee automaattisesti koko tietojoukon, mukaan lukien otsikot.

Toistaiseksi tässä opetusohjelmassa olemme nähneet, kuinka viitata erilaisiin soluihin eri tavoin.

Katsotaan nyt joitain tapoja, joilla voimme todella käyttää näitä tekniikoita jonkin työn tekemiseen.

Kopioi solut / alueet VBA: n avulla

Kuten mainitsin tämän opetusohjelman alussa, solun valitseminen ei ole välttämätöntä sen toimintojen suorittamiseksi. Tässä osiossa näet, miten kopioit soluja ja alueita jopa valitsematta niitä.

Aloitetaan yksinkertaisella esimerkillä.

Yksisolun kopiointi

Jos haluat kopioida solun A1 ja liittää sen soluun D1, alla oleva koodi tekee sen.

Sub CopyCell () -alue ("A1"). Copy Range ("D1") End Sub

Huomaa, että alueobjektin kopiointimenetelmä kopioi solun (aivan kuten Control +C) ja liittää sen määritettyyn kohteeseen.

Yllä olevassa esimerkkikoodissa kohde on määritetty samalla rivillä, jossa käytät kopiointimenetelmää. Jos haluat tehdä koodistasi vielä luettavamman, voit käyttää alla olevaa koodia:

Sub CopyCell () -alue ("A1"). Kopiointikohde: = Range ("D1") End Sub

Yllä olevat koodit kopioivat ja liittävät arvon sekä muotoilun/kaavat siihen.

Kuten olet ehkä jo huomannut, yllä oleva koodi kopioi solun valitsematta sitä. Riippumatta siitä, missä olet laskentataulukossa, koodi kopioi solun A1 ja liittää sen D1: een.

Huomaa myös, että yllä oleva koodi korvaa kaikki solussa D2 olevat koodit. Jos haluat Excelin ilmoittavan, jos solussa D1 on jo jotain korvaamatta sitä, voit käyttää alla olevaa koodia.

Sub CopyCell () If Range ("D1") "" then Response = MsgBox ("Haluatko korvata olemassa olevat tiedot", vbYesNo) End If If Response = vbKyllä sitten alue ("A1"). Kopioi alue ("D1 ") Lopeta, jos loppu

Kiinteän kokoisen alueen kopiointi

Jos haluat kopioida A1: D20 kohdassa J1: M20, voit käyttää alla olevaa koodia:

Sub CopyRange () -alue ("A1: D20"). Copy Range ("J1") End Sub

Kohdesolussa sinun tarvitsee vain määrittää vasemman yläkulman solun osoite. Koodi kopioi automaattisesti tarkan kopioidun alueen kohteeseen.

Voit käyttää samaa rakennetta tietojen kopioimiseen yhdeltä taulukolta toiselle.

Alla oleva koodi kopioi A1: D20 aktiivisesta taulukosta taulukkoon 2.

Sub CopyRange () -alue ("A1: D20"). Kopioi laskentataulukot ("Sheet2"). Alue ("A1") Loppuosa

Yllä oleva kopioi tiedot aktiiviselta taulukolta. Varmista siis, että tiedot sisältävä taulukko on aktiivinen arkki ennen koodin suorittamista. Turvallisuuden vuoksi voit myös määrittää laskentataulukon nimen, kun kopioit tietoja.

Sub CopyRange () -työarkit ("Sheet1"). Alue ("A1: D20"). Copy worksheets ("Sheet2"). Range ("A1") End Sub

Hyvä asia yllä olevassa koodissa on, että riippumatta siitä, mikä arkki on aktiivinen, se kopioi tiedot aina taulukosta 1 ja liittää sen taulukkoon 2.

Voit myös kopioida nimetyn alueen käyttämällä sen nimeä viittauksen sijasta.

Jos sinulla on esimerkiksi nimetty alue nimeltä 'SalesData', voit kopioida nämä tiedot Sheet2: een alla olevan koodin avulla.

Sub CopyRange () -alue ("SalesData"). Kopioi laskentataulukot ("Sheet2"). Range ("A1") End Sub

Jos nimetyn alueen laajuus on koko työkirja, sinun ei tarvitse olla taulukolla, jolla on nimetty alue tämän koodin suorittamiseksi. Koska nimetty alue on työkirjan kattama, voit käyttää sitä mistä tahansa taulukosta tällä koodilla.

Jos sinulla on taulukko nimeltä Table1, voit kopioida sen taulukkoon Sheet2 alla olevan koodin avulla.

Sub CopyTable () -alue ("Table1 [#All]"). Kopioi laskentataulukot ("Sheet2"). Range ("A1") End Sub

Voit myös kopioida alueen toiseen työkirjaan.

Seuraavassa esimerkissä kopioin Excel -taulukon (Taulukko1) Book2 -työkirjaan.

Sub CopyCurrentRegion () -alue ("Table1 [#All]"). Kopioi työkirjat ("Book2.xlsx"). Laskentataulukot ("Sheet1"). Range ("A1") End Sub

Tämä koodi toimii vain, jos työkirja on jo auki.

Muuttuvan kokoisen alueen kopioiminen

Yksi tapa kopioida vaihtelevan kokoisia alueita on muuntaa ne nimetyiksi alueiksi tai Excel -taulukkoksi ja käyttää koodeja kuten edellisessä osassa.

Mutta jos et voi tehdä sitä, voit käyttää alueobjektin CurrentRegion- tai End -ominaisuutta.

Alla oleva koodi kopioi nykyisen alueen aktiivisessa taulukossa ja liittää sen Sheet2: een.

Sub CopyCurrentRegion () -alue ("A1"). CurrentRegion.Copy -laskentataulukot ("Sheet2"). Range ("A1") End Sub

Jos haluat kopioida tietojoukon ensimmäisen sarakkeen viimeiseen täytettyyn soluun asti ja liittää sen Sheet2: een, voit käyttää alla olevaa koodia:

Sub CopyCurrentRegion () Alue ("A1", alue ("A1"). Loppu (xlDown)). Kopioi laskentataulukot ("Sheet2"). Alue ("A1") Lopeta ala

Jos haluat kopioida rivit ja sarakkeet, voit käyttää alla olevaa koodia:

Sub CopyCurrentRegion () Alue ("A1", Alue ("A1"). End (xlDown) .End (xlToRight)). Kopioi laskentataulukot ("Sheet2"). Alue ("A1") Loppuosa

Huomaa, että kaikki nämä koodit eivät valitse soluja suorituksen aikana. Yleensä löydät vain kourallisen tapauksia, joissa sinun on todella valittava solu/alue ennen sen käsittelemistä.

Alueiden määrittäminen objektimuuttujille

Toistaiseksi olemme käyttäneet solujen täydellistä osoitetta (kuten työkirjoja (“Book2.xlsx”). Laskentataulukoita (“Sheet1”). Alue (“A1”)).

Voit tehdä koodistasi hallittavamman määrittämällä nämä alueet objektimuuttujille ja käyttämällä sitten niitä.

Esimerkiksi alla olevassa koodissa olen määrittänyt lähde- ja kohdealueen objektimuuttujille ja sitten käyttänyt näitä muuttujia tietojen kopioimiseksi alueelta toiselle.

Sub CopyRange () Dim SourceRange As Range Dim DestinationRange As Range Set SourceRange = Työsivut ("Sheet1"). Alue ("A1: D20") Set DestinationRange = Worksheets ("Sheet2"). Range ("A1") SourceRange.Copy DestinationRange End Sub

Aloitamme ilmoittamalla muuttujat Range -objekteiksi. Sitten määritämme alueen näille muuttujille käyttämällä Set -käskyä. Kun alue on määritetty muuttujalle, voit käyttää muuttujaa.

Syötä tiedot seuraavaan tyhjään soluun (syöttölaatikon avulla)

Voit syöttää tietoja syöttökenttien avulla.

Oletetaan esimerkiksi, että sinulla on alla oleva tietojoukko ja haluat syöttää myyntitietueen, voit käyttää VBA: n syöttökenttää. Käyttämällä koodia voimme varmistaa, että se täyttää seuraavan tyhjän rivin tiedot.

Sub EnterData () Dim RefRange as Range Set RefRange = Range ("A1"). End (xlDown). Offset (1, 0) Set ProductCategory = RefRange.Offset (0, 1) Set Quantity = RefRange.Offset (0, 2) ) Set Amount = RefRange.Offset (0, 3) RefRange.Value = RefRange.Offset (-1, 0) .Arvo + 1 ProductCategory.Value = InputBox ("Tuoteluokka") Quantity.Value = InputBox ("Määrä") Amount.Value = InputBox ("Amount") End Sub

Yllä oleva koodi käyttää VBA -syöttöruutua saadakseen syötteet käyttäjältä ja syöttää sitten tulot määritettyihin soluihin.

Huomaa, että emme käyttäneet tarkkoja soluviittauksia. Sen sijaan olemme käyttäneet End- ja Offset -ominaisuutta löytääksemme viimeisen tyhjän solun ja täyttämällä sen tiedot.

Tämä koodi on kaukana käytettävästä. Jos esimerkiksi kirjoitat tekstimerkkijonon, kun syöttökenttä pyytää määrää tai määrää, huomaat, että Excel sallii sen. Voit tarkistaa If -ehdon avulla, onko arvo numeerinen vai ei, ja sallia sen vastaavasti.

Kierros solujen / alueiden läpi

Toistaiseksi olemme nähneet kuinka valita, kopioida ja syöttää tiedot soluihin ja alueisiin.

Tässä osiossa näemme, miten silmukan läpi alueen solujen/rivien/sarakkeiden joukko. Tästä voi olla hyötyä, kun haluat analysoida jokaisen solun ja suorittaa jonkin toiminnon sen perusteella.

Jos haluat esimerkiksi korostaa valinnan joka kolmas rivi, sinun on toistettava ja tarkistettava rivinumero. Samoin, jos haluat korostaa kaikki negatiiviset solut vaihtamalla fontin värin punaiseksi, sinun on kierrettävä läpi ja analysoitava kunkin solun arvo.

Tässä on koodi, joka kulkee valittujen solujen rivien läpi ja korostaa vaihtoehtoisia rivejä.

Sub HighlightAlternateRows () Dim Myrange As Range Dim Myrow as Range Set Myrange = Valinta jokaiselle Myrow In Myrange.Rows If Myrow.Row Mod 2 = 0 Sitten Myrow.Interior.Color = vbCyan End If Next Myrow End Sub

Yllä oleva koodi käyttää MOD -toimintoa valinnan rivinumeron tarkistamiseen. Jos rivinumero on parillinen, se korostetaan syaanilla värillä.

Tässä on toinen esimerkki, jossa koodi kulkee jokaisen solun läpi ja korostaa solut, joilla on negatiivinen arvo.

Sub HighlightAlternateRows () Dim Myrange as Range Dim Mycell as Range Set Myrange = Valinta jokaiselle Mycellille Myrangeissa Jos Mycell <0 Sitten Mycell.Interior.Color = vbPunainen loppu jos seuraava Mycell End Sub

Huomaa, että voit tehdä saman käyttämällä ehdollista muotoilua (joka on dynaaminen ja parempi tapa tehdä tämä). Tämä esimerkki on tarkoitettu vain osoittamaan, kuinka silmukointi toimii solujen ja alueiden kanssa VBA: ssa.

Minne laittaa VBA -koodi

Mietitkö, mihin VBA -koodi menee Excel -työkirjassa?

Excelissä on VBA -taustaohjelma, jota kutsutaan VBA -editoriksi. Sinun on kopioitava ja liitettävä koodi VB Editor -moduulin koodi -ikkunaan.

Tässä on seuraavat vaiheet:

1. Siirry Kehittäjä -välilehdelle.
2. Napsauta Visual Basic -vaihtoehtoa. Tämä avaa VB -editorin taustaohjelmaan.
3. Napsauta hiiren kakkospainikkeella mitä tahansa työkirjan objektia VB Editorin Project Explorer -ruudussa, johon haluat lisätä koodin.Jos et näe Project Exploreria, siirry Näytä -välilehteen ja napsauta Project Explorer.
4. Siirry Lisää -kohtaan ja napsauta Moduuli. Tämä lisää moduuliobjektin työkirjaasi.
5. Kopioi ja liitä koodi moduuli -ikkunaan.