excel vba именованный диапазон

Я пытаюсь определить именованный диапазон в Excel с помощью VBA. В принципе, у меня есть номер столбца переменной. Затем выполняется цикл для определения первой пустой ячейки в этом конкретном столбце. Теперь я хочу определить именованный диапазон из строки 2 этого конкретного столбца в последнюю ячейку с данными в этом столбце (первая пустая ячейка — 1).

Например, указан столбец 5, который содержит 3 значения. Тогда мой диапазон был бы (2,5) (4,5), если я прав. Мне просто интересно, как указать этот диапазон, используя только целые числа вместо (E2: E4). Возможно ли это?

Я нашел этот код для определения именованного диапазона:

Может ли кто-нибудь подтолкнуть меня в правильном направлении, чтобы указать этот диапазон, используя только целые числа?

У меня есть именованный диапазон col_9395 это целый столбец. Я хочу установить диапазон в этом диапазоне имен. Я хочу, чтобы диапазон начинался с строки 3 до строки 200 того же столбца. Каков наилучший способ сделать это?

Исходная рабочая строка без Именованного диапазона:

Это код, который я пробовал без везения:

vba excel-vba excel named-ranges

4 ответа

1 Решение AntiDrondert [2018-04-19 18:00:00]

Возможно, это неправильно, но я считаю это самым простым:

1 QHarr [2018-04-19 17:54:00]

Вы можете видеть логику с

1 Vityata [2018-04-19 17:57:00]

Проблема с SpecialCells и назначение им заключается в том, что если нет ячеек определенного типа, это вызывает ошибку.

Таким образом, перед установкой rngBlnk в специальные ячейки выполняется проверка с помощью WorksheetFunction.CountBlank()>0 .

То, что я сделал, это присвоить ваш именованный диапазон переменной (просто для упрощения ссылок). Затем, используя свойство Range() , я использовал 3rd и 200th строку вашего именованного диапазона, чтобы задать диапазон для поиска пустых ячеек.

Идея заключается в том, что это поможет вам в том случае, если ваш именованный диапазон не просто целый столбец. Он получит относительную 3-ю и 200-ю строку из вашего именованного диапазона.

Диапазоны легче идентифицировать по имени, чем с помощью нотации A1. Ranges are easier to identify by name than by A1 notation. Чтобы присвоить имя выбранному диапазону, щелкните поле имени с левой стороны строки формул, введите имя и нажмите клавишу ВВОД. To name a selected range, click the name box at the left end of the formula bar, type a name, and then press ENTER.

Примечание. Существует два типа именованных диапазонов: именованный диапазон книги и именованный диапазон определенного листа. Note There are two types of named ranges: Workbook Named Range and WorkSHEET Specific Named Range.

Именованный диапазон книги Workbook Named Range

Именованный диапазон книги относится к определенному диапазону в любом месте книги (применяется глобально). A Workbook Named Range references a specific range from anywhere in the workbook (it applies globally).

Как создать именованный диапазон книги: How to Create a Workbook Named Range:

Как указано выше, обычно он создается путем ввода имени в поле «Имя» с левой стороны строки формул. As explained above, it is usually created entering the name into the name box to the left end of the formula bar. Обратите внимание, что имя не может содержать пробелов. Note that no spaces are allowed in the name.

Именованный диапазон определенного листа WorkSHEET Specific Named Range

Именованный диапазон определенного листа относится к диапазону конкретного листа и не является глобальным для всех листов в книге. A WorkSHEET Specific Named Range refers to a range in a specific worksheet, and it is not global to all worksheets within a workbook. Сослаться на такой именованный диапазон с этого же листа можно просто с помощью имени, но из другого листа потребуется использовать имя листа с добавлением «!» и имени диапазона (пример: диапазон «Имя» «= Лист1!Имя»). You can refer to this named range by just the name in the same worksheet, but from another worksheet you must use the worksheet name including «!» the name of the range (example: the range «Name» «=Sheet1!Name»).

Преимущество заключается в возможности использования кода VBA для создания новых листов с одинаковыми именами для одних и тех же диапазонов на этих листах без возникновения ошибки, сообщающей, что имя уже используется. The benefit is that you can use VBA code to generate new sheets with the same names for the same ranges within those sheets without getting an error saying that the name is already taken.

Как создать именованный диапазон определенного листа: How to Create a WorkSHEET Specific Named Range:

1. Выделите диапазон, которому нужно присвоить имя. Select the range you want to name.
2. Перейдите на вкладку «Формулы» на ленте Excel в верхней части окна. Click on the «Formulas» tab on the Excel Ribbon at the top of the window.
3. Нажмите кнопку «Присвоить имя» на вкладке формул. Click «Define Name» button in the Formula tab.
4. В диалоговом окне «Создание имени» в поле «Область» выберите конкретный лист, где расположен диапазон, которому нужно присвоить имя (например, «Лист1»), чтобы связать имя с этим листом. In the «New Name» dialogue box, under the field «Scope» choose the specific worksheet that the range you want to define is located (i.e. «Sheet1»)- This makes the name specific to this worksheet. Если выбрать вариант «Книга», это будет имя книги. If you choose «Workbook» then it will be a WorkBOOK name).

Пример именованного диапазона определенного листа: выделенный диапазон A1:A10 для присвоения имени. Example, of WorkSHEET Specific Named Range: Selected range to name are A1:A10

Выбранное имя диапазона — «Имя». В пределах одного листа ссылайтесь на именованный диапазон, просто введя в ячейку «=Имя». Из другого листа ссылайтесь на диапазон определенного листа, указав в ячейке имя листа: «= Лист1!Имя». Chosen name of range is «name» within the same worksheet refer to the named name mere by entering the following in a cell «=name», from a different worksheet refer to the worksheet specific range by included the worksheet name in a cell «=Sheet1!name».

Ссылка на именованный диапазон Referring to a Named Range

В следующем примере выполняется ссылка на диапазон с именем MyRange в книге с именем MyBook.xls. The following example refers to the range named «MyRange» in the workbook named «MyBook.xls.»

В следующем примере выполняется ссылка на диапазон определенного листа с именем Sheet1!Sales в книге с именем Report.xls. The following example refers to the worksheet-specific range named «Sheet1!Sales» in the workbook named «Report.xls.»

Чтобы выбрать именованный диапазон, используйте метод GoTo, который активирует книгу и лист, а затем выбирает диапазон. To select a named range, use the GoTo method, which activates the workbook and the worksheet and then selects the range.

В следующем примере показано, как можно написать эту же процедуру для активной книги. The following example shows how the same procedure would be written for the active workbook.

Пример кода предоставил: Деннис Валлентин VSTO & .NET & Excel Sample code provided by: Dennis Wallentin, VSTO & .NET & Excel

В этом примере в качестве формулы для проверки данных используется именованный диапазон. This example uses a named range as the formula for data validation. В этом примере данные проверки должны быть на листе 2 в диапазоне A2:A100. This example requires the validation data to be on Sheet 2 in the range A2:A100. Они используются для проверки данных, введенных на листе 1 в диапазоне D2:D10. This validation data is used to validate data entered on Sheet 1 in the range D2:D10.

Циклический переход по ячейкам в именованном диапазоне Looping Through Cells in a Named Range

В следующем примере выполняется циклический переход по каждой ячейке именованного диапазона с помощью цикла For Each. Next. The following example loops through each cell in a named range by using a For Each. Next loop. Если значение любой ячейки в диапазоне превышает значение Limit , цвет ячейки изменяется на желтый. If the value of any cell in the range exceeds the value of Limit , the cell color is changed to yellow.

Об участнике About the Contributor

Деннис Валлентин (Dennis Wallentin) — автор блога VSTO & .NET & Excel, посвященного решениям .NET Framework для Excel и службам Excel. Dennis Wallentin is the author of VSTO & .NET & Excel, a blog that focuses on .NET Framework solutions for Excel and Excel Services. Деннис разрабатывает решения Excel более 20 лет и также является соавтором книги «Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel, VBA, and .NET (2nd Edition)». Dennis has been developing Excel solutions for over 20 years and is also the coauthor of «Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel, VBA and .NET (2nd Edition).»

Поддержка и обратная связь Support and feedback

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Have questions or feedback about Office VBA or this documentation? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь. Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.