|
Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:
Jak využít k výběru dat CurrentRegion a UsedRange v Excel VBA.
Jak vybírat nesourodou oblast v případě načtení dat. Přitom nemuset zjišťovat pro daný řádek (sloupec poslední řádek/sloupec). Pro přehlednost je článek rozdělen na jednotlivé kapitoly:
Mnohdy je výhodné oblast dat označit (vyselektovat) využitím CurrentRegion (ne CurentRegion) a UsedRange. Rozdíl bude snad pochopitelný z ukázky:
Z obrázků snad je rozdíl zřejmý. Případně pokud kódy otestujete, bude to ještě jasnější.
Pokud data (tabulku) máte označeny jako tabulka. Doporučuji článek Tabulka jako tabulka využitím VBA.
V listě mám několik tabulek a potřebuji vybrat související oblast.
' Vyberu si buňku a označím související oblast (náhrada Ctrl + A)
Range("A1").CurrentRegion.Select
' Nemusí jít o horní roh tabulky kterou vybíráme
Range("F5").CurrentRegion.Select
' Jiný list
Worksheets("List3").Activate
Range("F12").CurrentRegion.Select
' Chyba pokud nelze oblast označit (okolo buňky jsou jen prázdné buňky)
ActiveCell.CurrentRegion.Select
Sloučené buňky.
Jak na úpravu velikosti tabulky. Nebo-li někdy potřebují oblast bez záhlaví, zápatí. Bez posledního sloupce, atd.
' Pouze bez záhlaví tabulky
Range("F20").CurrentRegion.Offset(1).Resize(Range("F20").CurrentRegion.Rows.Count - 1).Select
' Pokud záhlaví i zápatí
Range("F20").CurrentRegion.Offset(1).Resize(Range("F20").CurrentRegion.Rows.Count - 2).Select
' První rádek, poslední sloupec
Range("F36").CurrentRegion.Offset(1).Resize(Range("F36").CurrentRegion.Rows.Count - 1, Range("F36").CurrentRegion.Columns.Count - 1).Select
'' Možno využít i přes Selection
Využít Offset a Resize.
Potřebuji-li v oblasti zjistit: poslední řádek, poslední sloupec.
Dim rng As Range
Dim PosledniRadek As Long, PosledniSloupec As Integer
Dim PosledniBunka As Range
Set rng = Range("F20").CurrentRegion
' 'Poslední řadek, sloupec buňka
MsgBox (rng.Rows.Count)
MsgBox (rng.Columns.Count)
Set PosledniBunka = rng(rng.Rows.Count, rng.Columns.Count)
PosledniBunka.Select
' Lze buňku upravit označit atd.
Potřebujete-li v oblasti vybrat buňky splňují požadované kritérium. lze využít SpecialCells.
' Ukázka na později
Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks).Select
' vybranou oblast lze například barevně označit ;)
Jak na SpecialCell se podívám v nějakém jiném článku (pokud se nerozhodnu doplnit tento). Zatím si jen odložím podklady:
RangeObject.SpecialCells(Type, Value)
Hodnoty pro Type:
xlCellTypeAllFormatConditions
xlCellTypeAllValidation
xlCellTypeBlanks
xlCellTypeComments
xlCellTypeConstants
xlCellTypeFormulas
xlCellTypeLastCell
xlCellTypeSameFormatConditions
xlCellTypeSameValidation
xlCellTypeVisible
Hodnoty pro Value:
xlErrors
xlLogical
xlNumbers
xlTextValues
Potřebuji vyplnit data, nejčastěji pokud dostanu výstup, ze starších kontingenček, kde ještě Excel neuměl opakovat řádky. Nebo rozdělím buňky a řádky x buněk je prázdných ač by měli obsahovat stejná data.
Než složite popisovat obrázek řekne více, mám a potřebuji:
Řešením jsem se inspiroval, někde na stackoverflow:
Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
Range("A1").CurrentRegion.Value = Range("A1").CurrentRegion.Value
Problém nastane pokud se nenajde prázdná buňka tak lze ošetřit přeskočením chyby:
On Error Resume Next
' Protože když není žádná prázdná buňka skončí chybou :(
' Tak jen chybu přeskočí
Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
Range("A1").CurrentRegion.Value = Range("A1").CurrentRegion.Value
Využití oblast. UsedRange bere do úvahy i design buňky (např.: barva pozadí)
' Výběr využité oblasti
UsedRange.Select
'' Výběr oblasti aktivní list
ActiveSheet.UsedRange.Select
' Jiný list
List3.Activate
List3.UsedRange.Select
Pokud budu chtít vědět rozsah využité oblasti na listě, řádky, sloupce ....
MsgBox ActiveSheet.UsedRange.Rows.Count
MsgBox ActiveSheet.UsedRange.Columns.Count
MsgBox (ActiveSheet.UsedRange.Address)
Pro testování můžete využít i Debug.Print
UsedRange.SpecialCells(xlCellTypeBlanks).Select
Další ukázky v přípravě
S tématem souvisí další témata o kterých jsem již napsal:
Soubor ke stažení zdarma je v přípravě. Soubor využívá makra.
Napadá vás, co se dá k tématu doplnit? Případné tipy na doplnění.
Článek byl aktualizován: 19.09.2020 11:06
Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.
Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.
Můžete být prvními co zanechají smysluplný komentář.
Pomohl Vám návod? Sdílejte na Facebooku, G+ |
||
LinkedIn... |
Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2021 |