Тази статия ще покаже как да създадете динамичен диапазон в Excel VBA.
Обявяването на определен диапазон от клетки като променлива в Excel VBA ни ограничава да работим само с тези конкретни клетки. Чрез деклариране на динамични диапазони в Excel, ние печелим много по -голяма гъвкавост по отношение на нашия код и функционалността, която той може да изпълнява.
Референтни диапазони и клетки
Когато се позоваваме на Range или Cell обект в Excel, обикновено се отнасяме към тях чрез твърдо кодиране в реда и колоните, които ни трябват.
Range Property
Използвайки свойството Range, в примерните редове на кода по -долу, можем да извършим действия в този диапазон, като например промяна на цвета на клетките или удебеляване на клетките.
| 12 | Диапазон ("A1: A5"). Font.Color = vbRedДиапазон ("A1: A5"). Font.Bold = True |
Клетъчна собственост
По подобен начин можем да използваме свойството на клетките, за да се позоваваме на диапазон от клетки, като директно препращаме към реда и колоната в свойството клетки. Редът винаги трябва да бъде число, но колоната може да бъде число или буква, затворена в кавички.
Например, адресът на клетката A1 може да бъде посочен като:
| 1 | Клетки (1,1) |
Или
| 1 | Клетки (1, "А") |
За да използваме свойството Cells за препратка към диапазон от клетки, трябва да посочим началото на диапазона и края на диапазона.
Например за референтния диапазон A1: A6 бихме могли да използваме този синтаксис по -долу:
| 1 | Обхват (клетки (1,1), клетки (1,6) |
След това можем да използваме свойството Cells за извършване на действия в диапазона съгласно примерните редове на кода по -долу:
| 12 | Диапазон (клетки (2, 2), клетки (6, 2)). Font.Color = vbRedДиапазон (клетки (2, 2), клетки (6, 2)). Font.Bold = True |
Динамични диапазони с променливи
Тъй като размерът на нашите данни се променя в Excel (т.е. използваме повече редове и колони, отколкото диапазоните, които сме кодирали), би било полезно, ако диапазоните, за които говорим в нашия код, също трябва да се променят. Използвайки обекта Range по -горе, можем да създадем променливи за съхраняване на максималния брой редове и колони в областта на работния лист на Excel, който използваме, и да използваме тези променливи за динамично коригиране на обекта Range, докато кодът работи.
Например
| 1234 | Dim lRow като цяло числоЗатъмнете lCol като цяло числоlRow = Range ("A1048576"). End (xlUp) .RowlCol = Обхват ("XFD1"). Край (xlToLeft). Колона |
Последен ред в колоната
Тъй като в работния лист има 1048576 реда, променливата lRow ще отиде в долната част на листа и след това ще използва специалната комбинация от клавиша за край плюс клавиша със стрелка нагоре, за да преминете към последния ред, използван в работния лист - това ще ни даде номера на реда, от който се нуждаем в нашия диапазон.
Последна колона в ред
По същия начин lCol ще се премести в колона XFD, която е последната колона в работен лист, и след това използва специалната комбинация от клавиши на клавиша за край плюс клавиша със стрелка наляво, за да отидете до последната колона, използвана в работния лист - това ще ни даде номер на колоната, от която се нуждаем в нашия диапазон.
Следователно, за да получим целия диапазон, който се използва в работния лист, можем да изпълним следния код:
| 1234567891011 | Sub GetRange ()Dim lRow As IntegerDim lCol As IntegerDim rng As RangelRow = Range ("A1048576"). End (xlUp) .Row'използвайте lRow, за да намерите последната колона в диапазонаlCol = Диапазон ("XFD" & lRow). Край (xlToLeft). КолонаЗадайте rng = диапазон (клетки (1, 1), клетки (lRow, lCol))'msgbox, за да ни покаже обхватаMsgBox "Диапазонът е" & rng.AddressEnd Sub |
SpecialCells - LastCell
Можем също да използваме метода SpecialCells на Range Object, за да получим последния ред и колона, използвани в работен лист.
| 123456789101112 | Sub UseSpecialCells ()Dim lRow As IntegerDim lCol As IntegerDim rng As RangeDim rng Започнете като диапазонЗадайте rngBegin = Диапазон ("A1")lRow = rngBegin.SpecialCells (xlCellTypeLastCell) .RowlCol = rngBegin.SpecialCells (xlCellTypeLastCell).Задайте rng = диапазон (клетки (1, 1), клетки (lRow, lCol))'msgbox, за да ни покаже обхватаMsgBox "Диапазонът е" & rng.AddressEnd Sub |
UsedRange
Методът на използвания диапазон включва всички клетки, които имат стойности в текущия работен лист.
| 123456 | Sub UsedRangeExample ()Dim rng As RangeЗадайте rng = ActiveSheet.UsedRange'msgbox, за да ни покаже обхватаMsgBox "Диапазонът е" & rng.AddressEnd Sub |
CurrentRegion
Текущият регион се различава от UsedRange по това, че разглежда клетките, обграждащи клетка, която сме декларирали като начален диапазон (т.е. променливата rngBegin в примера по -долу), и след това разглежда всички клетки, които са „прикрепени“ или свързани към тази декларирана клетка. Ако се появи празна клетка в ред или колона, CurrentRegion ще спре да търси допълнителни клетки.
| 12345678 | Sub CurrentRegion ()Dim rng As RangeDim rng Започнете като диапазонЗадайте rngBegin = Диапазон ("A1")Задайте rng = rngBegin.CurrentRegion'msgbox, за да ни покаже обхватаMsgBox "Диапазонът е" & rng.AddressEnd Sub |
Ако използваме този метод, трябва да се уверим, че всички клетки в диапазона, от който се нуждаете, са свързани без празни редове или колони между тях.
Именован диапазон
Можем също така да се позоваваме на Намерени диапазони в нашия код. Назованите диапазони могат да бъдат динамични, доколкото при актуализиране или вмъкване на данните, името на диапазона може да се промени, за да включва новите данни.
Този пример ще промени шрифта на получер за името на диапазона „Януари“
| 12345 | Sub RangeNameExample ()Затъмнете като обхватЗадайте rng = диапазон ("януари")rng.Font.Bold = = ВярноEnd Sub |
Както ще видите на снимката по -долу, ако ред е добавен към името на диапазона, тогава името на диапазона автоматично се актуализира, за да включи този ред.
Ако след това отново стартираме примерния код, обхватът, засегнат от кода, ще бъде C5: C9, докато в първия случай щеше да бъде C5: C8.
Таблици
Можем да се позоваваме на таблици (щракнете за повече информация относно създаването и манипулирането на таблици във VBA) в нашия код. Тъй като данните в таблицата в Excel се актуализират или променят, кодът, който се отнася до таблицата, след това ще се отнася до актуализираните данни от таблицата. Това е особено полезно, когато се позовавате на обобщени таблици, които са свързани към външен източник на данни.
Използвайки тази таблица в нашия код, можем да се позоваваме на колоните на таблицата чрез заглавията във всяка колона и да изпълняваме действия върху колоната според тяхното име. Тъй като редовете в таблицата се увеличават или намаляват според данните, диапазонът на таблицата ще се коригира съответно и нашият код ще продължи да работи за цялата колона в таблицата.
Например:
| 123 | Sub DeleteTableColumn ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListColumns ("Supplier"). DeleteEnd Sub |
