Munka cellák és tartományok Excel VBA (Select, Copy, Move, Edit)

amikor dolgozik Excel, a legtöbb időt töltött a munkalap területen – foglalkozó sejtek és tartományok.

és ha azt szeretnénk, hogy automatizálják a munkát az Excel segítségével VBA, meg kell tudni, hogyan kell dolgozni a sejtek és tartományok segítségével VBA.

van egy csomó különböző dolog, amit tehetünk tartományok VBA (például válassza ki, másolás, áthelyezés, szerkesztés, stb .. ).

tehát, hogy fedezze ezt a témát, fogom törni ezt a bemutatót szakaszokra, és megmutatja,hogyan kell dolgozni a sejtek és tartományok Excel VBA példákkal.,

kezdjük.

Ha érdekli a tanulás VBA az egyszerű módja, nézd meg az Online Excel VBA képzés.

a Cella Kiválasztása / Tartomány Excel VBA

dolgozni a cellák, illetve tartományok az Excel VBA, nem kell, hogy válassza ki.

a legtöbb esetben jobb, ha nem választ ki cellákat vagy tartományokat (amint látni fogjuk).,

ennek ellenére fontos, hogy végigmenjen ezen a szakaszon, és megértse, hogyan működik. Ez döntő fontosságú lesz a VBA tanulásban, és sok itt tárgyalt fogalmat fogunk használni az oktatóprogram során.

Tehát kezdjük egy nagyon egyszerű példával.

egyetlen cella kiválasztása VBA

használatával ha egyetlen cellát szeretne kiválasztani az aktív lapon (mondjuk A1), akkor az alábbi kódot használhatja:

Sub SelectCell()Range("A1").SelectEnd Sub

a fenti kódnak kötelező ” Sub ” és “End Sub” része van, valamint egy kódsor, amely kiválasztja az A1 cellát.,

tartomány(“A1”) megmondja a VBA-nak annak a cellának a címét, amelyre hivatkozni akarunk.

A kijelölés a Tartományobjektum egyik módszere, majd kiválasztja a Tartományobjektumban megadott cellákat/tartományt. A cella hivatkozásokat kettős idézetekbe kell foglalni.

Ez a kód hibát mutat, ha egy diagramlap aktív lap. A diagramlap diagramokat tartalmaz, amelyeket nem használnak széles körben. Mivel nincs benne cellák/tartományok, a fenti kód nem tudja kiválasztani, és végül hibát mutat.

vegye figyelembe, hogy mivel a cellát az aktív lapon szeretné kiválasztani, csak meg kell adnia a cellacímet.,

de ha egy másik lapon szeretné kiválasztani a cellát (mondjuk Sheet2), először aktiválnia kell a Sheet2-t, majd ki kell választania a cellát.

Sub SelectCell()Worksheets("Sheet2").ActivateRange("A1").SelectEnd Sub

hasonlóképpen aktiválhat egy munkafüzetet is, majd aktiválhat egy adott munkalapot, majd kiválaszthat egy cellát.

Sub SelectCell()Workbooks("Book2.xlsx").Worksheets("Sheet2").ActivateRange("A1").SelectEnd Sub

vegye figyelembe, hogy a munkafüzetekre való hivatkozáskor a teljes nevet a fájlkiterjesztéssel együtt kell használnia (.xlsx a fenti kódban). Abban az esetben, ha a munkafüzet soha nem mentésre került, nem kell a fájlkiterjesztést használnia.,

most ezek a példák nem nagyon hasznosak, de később látni fogja ebben a bemutatóban, hogyan használhatjuk ugyanazokat a fogalmakat a cellák másolásához és beillesztéséhez Excel-ben (VBA használatával).

ahogy kiválasztunk egy cellát, kiválaszthatunk egy tartományt is.

tartomány esetén lehet rögzített mérettartomány vagy változó mérettartomány.

rögzített mérettartományban tudni lehet, hogy mekkora a tartomány, és a pontos méretet a VBA kódban is felhasználhatja. De egy változó méretű tartományban, fogalmad sincs, milyen nagy a tartomány, és egy kis VBA varázslatot kell használnod.

lássuk, hogyan kell ezt megtenni.,

Fix méretű tartomány kiválasztása

itt van a kód, amely kiválasztja az A1:D20 tartományt.

Sub SelectRange()Range("A1:D20").SelectEnd Sub

ennek másik módja az alábbi kód használata:

Sub SelectRange()Range("A1", "D20").SelectEnd Sub

a fenti kód a bal felső cellacímet (A1) és a jobb alsó cellacímet (D20) veszi fel, majd kiválasztja a teljes tartományt. Ez a technika akkor válik hasznossá, ha variably méretű tartományokkal dolgozik (amint látni fogjuk, amikor a végső tulajdonság később szerepel ebben a bemutatóban).,

Ha azt szeretné, hogy a kiválasztás egy másik munkafüzetben vagy egy másik munkalapon történjen, akkor meg kell mondania a VBA-nak ezeknek az objektumoknak a pontos nevét.

például az alábbi kód az A1:D20 tartományt választja ki a Sheet2 munkalapon a Book2 munkafüzetben.

Sub SelectRange()Workbooks("Book2.xlsx").Worksheets("Sheet1").ActivateRange("A1:D20").SelectEnd Sub

most mi van, ha nem tudja, hány sor van. Mi van, ha ki szeretné választani az összes cellát, amelynek értéke van benne.

ezekben az esetekben a következő szakaszban bemutatott módszereket kell használnia (variably méretű tartomány kiválasztásakor).,

változó méretű tartomány kiválasztása

különböző módokon választhat ki egy cellatartományt. A választott módszer attól függ, hogy az adatok hogyan vannak felépítve.

ebben a szakaszban néhány hasznos technikát fogok lefedni, amelyek valóban hasznosak, ha a VBA tartományokkal dolgozik.

válassza a CurrentRange tulajdonság használata

lehetőséget azokban az esetekben, amikor nem tudja, hány sor/oszlop rendelkezik az adatokkal, használhatja a Tartományobjektum CurrentRange tulajdonságát.

a CurrentRange tulajdonság az adattartományban lévő összes szomszédos töltött cellát lefedi.,

Az alábbiakban az a kód, amely kiválasztja az aktuális régiót, amely az A1 cellát tartja.

Sub SelectCurrentRegion()Range("A1").CurrentRegion.SelectEnd Sub

a fenti módszer akkor jó, ha az összes adat táblázatként üres sorok/oszlopok nélkül van benne.

de ha üres sorok/oszlopok vannak az adatokban, akkor az üres sorok/oszlopok után nem választja ki azokat. Az alábbi képen a CurrentRegion kód a 10. sorig választja ki az adatokat, mivel a 11. sor üres.

ilyen esetekben érdemes a Munkalapobjektum UsedRange tulajdonságát használni.,

válassza ki a UsedRange Property

Userrange segítségével bármely megváltozott cellára hivatkozhat.

tehát az alábbi kód kiválasztja az összes használt cellát az aktív lapon.

Sub SelectUsedRegion()ActiveSheet.UsedRange.SelectEnd Sub

vegye figyelembe, hogy ha távoli cellája van, akkor a fenti kód és az összes sejt figyelembe veszi, amíg a használt cellát ki nem választják.

válassza ki a

Végtulajdonságot, ez a rész nagyon hasznos.

az End tulajdonság lehetővé teszi az utolsó kitöltött cella kiválasztását., Ez lehetővé teszi, hogy utánozza a hatását ellenőrzés Le / Fel nyíl gombot, vagy ellenőrizzék a jobb / bal gombokat.

próbáljuk megérteni ezt egy példa segítségével.

tegyük fel, hogy van egy adatkészlete az alábbiak szerint, és gyorsan ki szeretné választani az utolsó kitöltött cellákat az A. oszlopban.

a probléma itt az, hogy az adatok megváltozhatnak, és nem tudja, hány cellát töltöttek be. Ha ezt a billentyűzet használatával kell elvégeznie, kiválaszthatja az A1 cellát, majd használja a Control + Down nyíl billentyűt, majd kiválasztja az oszlop utolsó kitöltött celláját.

most nézzük meg, hogyan kell ezt megtenni a VBA használatával., Ez a technika akkor hasznos, ha gyorsan át szeretne ugrani az utolsó kitöltött cellára egy változó méretű oszlopban

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

a fenti kód az A. oszlopban az utolsó kitöltött cellára ugrik.

hasonlóképpen használhatja a végét(xlToRight), hogy ugorjon egy sorban az utolsó kitöltött cellára.

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

most mi van, ha a teljes oszlopot szeretné kiválasztani az utolsó kitöltött cellába ugrás helyett.,

ezt az alábbi kóddal teheti meg:

Sub SelectFilledCells()Range("A1", Range("A1").End(xlDown)).SelectEnd Sub

a fenti kódban a kiválasztandó cella első és utolsó hivatkozását használtuk. Nem számít, hány töltött sejt van, a fenti kód kiválasztja az összeset.

emlékezzen a fenti példára, ahol az A1:D20 tartományt a következő kódsor használatával választottuk ki:

tartomány(“A1″,”D20”)

itt az A1 volt a bal felső cella, a D20 pedig a jobb alsó cella a tartományban. Ugyanazt a logikát használhatjuk változó méretű tartományok kiválasztásában., De mivel nem tudjuk a jobb alsó cella pontos címét, a vég tulajdonságot használtuk, hogy megszerezzük.

tartományban(“A1”, tartomány(“A1”).End(xlDown)), “A1” az első cellára és tartományra(“A1”) utal.Az End (xlDown) az utolsó cellára utal. Mivel mindkét hivatkozást megadtuk, a kiválasztási módszer kiválasztja az összes cellát e két hivatkozás között.

hasonlóképpen kiválaszthat egy teljes adatkészletet is, amelynek több sora és oszlopa van.

az alábbi kód kiválasztja az összes kitöltött sort / oszlopot az A1 cellából kiindulva.,

Sub SelectFilledCells()Range("A1", Range("A1").End(xlDown).End(xlToRight)).SelectEnd Sub

a fenti kódban tartományt használtunk (“A1”).Vége (xlDown).Vége (xlToRight), hogy a referencia a jobb alsó töltött cella az adatbázisba.

különbség a CurrentRegion és az End között

Ha kíváncsi, miért használja az End tulajdonságot a kitöltött tartomány kiválasztásához, amikor a CurrentRegion tulajdonságunk van, hadd mondjam el a különbséget.

az End tulajdonsággal megadhatja a start cellát., Például, ha az adatai A1:D20-ban vannak, de az első sor fejlécek, akkor az End tulajdonság segítségével kiválaszthatja az adatokat fejlécek nélkül (az alábbi kóddal).

Sub SelectFilledCells()Range("A2", Range("A2").End(xlDown).End(xlToRight)).SelectEnd Sub

de a CurrentRegion automatikusan kiválasztja a teljes adatkészletet, beleértve a fejléceket is.

eddig ebben a bemutatóban láttuk, hogyan utalhatunk egy cellatartományra különböző módon.

most nézzük meg néhány módot, ahol ténylegesen használhatjuk ezeket a technikákat, hogy elvégezzünk néhány munkát.,

másolja a cellákat/tartományokat a VBA

használatával amint azt a bemutató elején említettem,a cellák kiválasztása nem szükséges a műveletek végrehajtásához. Ebben a részben láthatja, hogyan másolhatja a cellákat vagy a tartományokat anélkül, hogy ezeket kiválasztaná.

kezdjük egy egyszerű példával.

egyetlen cella másolása

Ha az A1 cellát szeretné másolni, majd beilleszteni a D1 cellába, az alábbi kód megteszi.

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

vegye figyelembe, hogy a tartományobjektum másolási módszere másolja a cellát (csakúgy, mint a Control +C), majd beilleszti a megadott rendeltetési helyre.,

a fenti példakódban a rendeltetési hely ugyanabban a sorban van megadva, ahol a másolási módszert használja. Ha még olvashatóbbá szeretné tenni a kódot, használhatja az alábbi kódot:

Sub CopyCell()Range("A1").Copy Destination:=Range("D1")End Sub

a fenti kódok másolják és beillesztik az értéket, valamint a formázást/képleteket.

amint azt már észrevetted, a fenti kód átmásolja a cellát anélkül, hogy kiválasztaná. Nem számít, hol van a munkalapon, a kód átmásolja az A1 cellát, majd beilleszti a D1-re.

azt is vegye figyelembe, hogy a fenti kód felülírja a D2 cellában meglévő kódokat., Ha azt szeretné, hogy az Excel tudatja Önnel, hogy van-e már valami a D1 cellában anélkül, hogy felülírná, használhatja az alábbi kódot.

Fix méretű tartomány másolása

Ha A1:D20-at J1:M20-ban szeretné másolni, akkor az alábbi kódot használhatja:

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

a célcellában csak meg kell adnia a bal felső cella címét. A kód automatikusan átmásolja a pontos másolt tartományt a rendeltetési helyre.

ugyanazt a konstrukciót használhatja az adatok másolásához az egyik lapról a másikra.

az alábbi kód átmásolja az A1:D20-At az aktív lapról a Sheet2-re.,

Sub CopyRange()Range("A1:D20").Copy Worksheets("Sheet2").Range("A1")End Sub

a fentiek lemásolják az adatokat az aktív lapról. Tehát győződjön meg róla, hogy az adatokkal rendelkező lap az aktív lap a kód futtatása előtt. A biztonság érdekében megadhatja a munkalap nevét az adatok másolásakor is.

Sub CopyRange()Worksheets("Sheet1").Range("A1:D20").Copy Worksheets("Sheet2").Range("A1")End Sub

a fenti kódban az a jó, hogy függetlenül attól, hogy melyik lap aktív, mindig másolja az adatokat a Sheet1-ből, majd illessze be a Sheet2-be.

egy megnevezett tartományt is másolhat a hivatkozás helyett a neve használatával.,

például, ha van egy megnevezett tartománya, a “SalesData”, az alábbi kóddal másolhatja ezeket az adatokat Sheet2-re.

Sub CopyRange()Range("SalesData").Copy Worksheets("Sheet2").Range("A1")End Sub

Ha a megnevezett tartomány hatóköre a teljes munkafüzet, akkor nem kell a lapon lennie, amely a megnevezett tartományt futtatja ezt a kódot. Mivel a megnevezett tartomány a munkafüzethez van méretezve, bármelyik lapról elérheti ezt a kódot.

ha van táblázata a Table1 névvel, akkor az alábbi kóddal másolhatja azt a Sheet2-re.

Sub CopyTable()Range("Table1").Copy Worksheets("Sheet2").Range("A1")End Sub

egy tartományt más munkafüzetbe is másolhat.,

a következő példában az Excel táblát (Table1) másolom a Book2 munkafüzetbe.

Sub CopyCurrentRegion()Range("Table1").Copy Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1")End Sub

Ez a kód csak akkor működik, ha a munkafüzet már nyitva van.

változó méretű tartomány másolása

a változó méretű tartományok másolásának egyik módja az, hogy ezeket névtartományokká vagy Excel táblává konvertálják, és a kódokat az előző szakaszban bemutatott módon használják.

de ha ezt nem teheti meg, használhatja a tartományobjektum CurrentRegion vagy End tulajdonságát.

az alábbi kód átmásolja az aktuális régiót az aktív lapon, majd beilleszti a Sheet2-be.,

Sub CopyCurrentRegion()Range("A1").CurrentRegion.Copy Worksheets("Sheet2").Range("A1")End Sub

Ha meg szeretnénk másolni az első oszlop a meghatározott adatokat, amíg az utolsó kitöltött cellájában, majd illessze a Munkalap2, használd az alábbi kódot:

Sub CopyCurrentRegion()Range("A1", Range("A1").End(xlDown)).Copy Worksheets("Sheet2").Range("A1")End Sub

Ha meg szeretnénk másolni, a sor, valamint az oszlopok, használd az alábbi kódot:

Sub CopyCurrentRegion()Range("A1", Range("A1").End(xlDown).End(xlToRight)).Copy Worksheets("Sheet2").Range("A1")End Sub

Megjegyezzük, hogy az összes ezeket a kódokat ne jelölje ki a cellákat, miközben egyre kivégezték. Általánosságban elmondható, hogy csak egy maroknyi olyan esetet talál, ahol ténylegesen ki kell választania egy cellát/tartományt, mielőtt dolgozna rajta.,

tartományok hozzárendelése az Objektumváltozókhoz

eddig a cellák teljes címét használtuk (például munkafüzetek (“Book2.xlsx”).Munkalapok (“Sheet1”).Tartomány(“A1”).

a kód kezelhetőbbé tétele érdekében ezeket a tartományokat objektumváltozókhoz rendelheti, majd használhatja ezeket a változókat.

például az alábbi kódban a forrás-és céltartományt objektumváltozókhoz rendeltem, majd ezeket a változókat használtam az adatok egyik tartományból a másikba történő másolásához.

A változókat Tartományobjektumként deklaráljuk., Ezután hozzárendeljük a tartományt ezekhez a változókhoz a Set utasítás használatával. Miután a tartományt hozzárendelték a változóhoz, egyszerűen használhatja a változót.

adja meg az adatokat a következő üres cellába (beviteli mező használatával)

A beviteli mezőket használhatja az adatok megadásához.

például tegyük fel, hogy az alábbi adatkészlettel rendelkezik, és be szeretné írni az értékesítési rekordot, használhatja a VBA beviteli mezőjét. Egy kód használatával megbizonyosodhatunk arról, hogy kitölti az adatokat a következő üres sorban.,

a fenti kód A VBA beviteli mezőt használja, hogy megkapja a bemeneteket a felhasználótól, majd beírja a bemeneteket a megadott cellákba.

vegye figyelembe, hogy nem használtunk pontos cellahivatkozásokat. Ehelyett az End and Offset tulajdonságot használtuk, hogy megtaláljuk az utolsó üres cellát, és kitöltsük benne az adatokat.

Ez a kód messze nem használható. Például, ha szöveges karakterláncot ír be, amikor a beviteli mező mennyiséget vagy összeget kér, észreveszi, hogy az Excel lehetővé teszi. Használhatja az If feltételt annak ellenőrzésére, hogy az érték numerikus-e vagy sem, majd ennek megfelelően engedélyezheti.,

cellákon/tartományokon keresztül hurkolva eddig láthattuk, hogyan kell kiválasztani, másolni, és beírni az adatokat cellákba és tartományokba.

ebben a szakaszban látni fogjuk, hogyan kell hurkolni egy sor cellán/soron/oszlopon egy tartományban. Ez hasznos lehet, ha azt szeretnénk, hogy elemezze az egyes cellák, majd végre néhány művelet alapján.

például, ha ki szeretné emelni a Kiválasztás minden harmadik sorát, akkor át kell hurkolnia, majd ellenőriznie kell a sorszámot., Hasonlóképpen, ha az összes negatív cellát ki szeretné emelni a betűszín vörösre változtatásával,akkor át kell kapcsolnia az egyes cellák értékét.

itt van az a kód, amely a kiválasztott cellák sorain keresztül hurkol, majd kijelöli az alternatív sorokat.

Sub HighlightAlternateRows()Dim Myrange As RangeDim Myrow As RangeSet Myrange = SelectionFor Each Myrow In Myrange.RowsIf Myrow.Row Mod 2 = 0 ThenMyrow.Interior.Color = vbCyanEnd IfNext MyrowEnd Sub

a fenti kód a mod funkciót használja a sorszám ellenőrzéséhez a kiválasztásban. Ha a sorszám egyenletes, akkor cián színű lesz kiemelve.

itt van egy másik példa, ahol a kód átmegy minden cellán, és kiemeli azokat a cellákat, amelyeknek negatív értéke van.,

Sub HighlightAlternateRows()Dim Myrange As RangeDim Mycell As RangeSet Myrange = SelectionFor Each Mycell In MyrangeIf Mycell < 0 ThenMycell.Interior.Color = vbRedEnd IfNext MycellEnd Sub

vegye figyelembe, hogy ugyanezt teheti feltételes formázással (ami dinamikus és jobb módja ennek). Ez a példa csak arra szolgál, hogy megmutassa, hogyan működik a hurok a VBA celláival és tartományaival.

hová tegye a VBA kódot

kíváncsi, hogy hol van a VBA kód az Excel munkafüzetben?

az Excelnek van egy VBA backendje, a VBA szerkesztő. Be kell másolnia a kódot a VB szerkesztő modul kód ablakába.

itt vannak a lépések erre:

1. lépjen a fejlesztő fülre.,
2. kattintson a Visual Basic opcióra. Ez megnyitja a VB szerkesztő a backend.
3. A VB szerkesztő projekt Explorer ablaktáblájában kattintson a jobb gombbal a munkafüzet bármely objektumára, amelybe be kívánja illeszteni a kódot. Ha nem látja a Project Explorer programot, lépjen a Nézet fülre, majd kattintson a Project Explorer elemre.
4. ugrás a beszúráshoz, majd kattintson a modulra. Ez beszúr egy modulobjektumot a munkafüzethez.
5. másolja be a kódot a modul ablakába.,

A következő Excel oktatóanyagokat is kedvelheti:

• munka munkalapokkal a VBA segítségével.
• munka munkafüzetek segítségével VBA.
• felhasználó által definiált funkciók létrehozása Excel-ben.
• az Excel VBA következő hurokjához-kezdő útmutató példákkal.
• hogyan kell használni az Excel VBA InStr funkciót (gyakorlati példákkal).
• Excel VBA Msgbox.
• hogyan lehet makrót rögzíteni az Excelben.
• makró futtatása Excel-ben.
• Hogyan hozzunk létre egy bővítményt az Excelben.,
• Excel Personal Macro Workbook / Save & használjon makrókat minden munkafüzetben.
• Excel VBA események-egy egyszerű (és teljes) útmutató.
• Excel VBA hibakezelés.
• hogyan lehet az adatokat Excel-ben rendezni a VBA segítségével (lépésről lépésre).
• 24 hasznos Excel makró példák VBA kezdőknek (használatra kész).

---