Using SpecialCells in Excel VBA
The SpecialCells method of the Range class returns a new range that represents all the cells that match its arguments. This allows you to process a subset of the cells in a range, e.g. the cells that have comments, or those that are empty, or whose value is determined by a formula. The table below shows which subsets of the cells in the range or worksheet you may get:
| Data: | Numbers | Text | Logical | Empty |
| Formula:: | Numbers | Text | Logical | Error |
| LastCell | Comments | Conditional Formatting | Validation | Visible |
As an example, the code below returns a range containing all cells that contain numeric constants (values, not formulas — e.g. 20.4). This page discusses the details on how to use this important methods. First I will present usefull info on what will be returned and how to prevent errors in your code. After that, I will walk through the different types of cells you can obtain with it. The examples assume data in the active sheet as in the image on the right.
The above code returns $B$4 as the range containing numbers
How to use SpecialCells
When using SpecialCells you will run into a number of peculiarities which I will discuss here first.
SpecialCells in specified multicell range or the used range of the worksheet
Excel often, and also in the case of SpecialCells, assumes you want the used range of the worksheet if your specified range only contains a single cell. As an example, the below code returns everything in the UsedRange of the active worksheet: $B$4:$B$5,$A$4:$A$6,$C$5:$C$6,$A$8
If in the first example we specified as the range to work on as «B2:C3», no cells would have been found in that range. Summerizing:
| Start range | Result obtained from |
|---|---|
| Single cell | Used Range |
| Multiple cells, cells are found | Specified range |
| Any, cells are NOT found on the entire worksheet | Error 1004 |
| Note |
|---|
| The above applies to the majority of SpecialCells cases. For LastCell, Same Conditional Formatting and Same Validation it is different — see the corrsponding sections. |
Handling error 1004: No cells were found
If we would ask for cells containing logical values (TRUE or FALSE) no cells would have been returned. Instead, without error handling, the user would get the error message on the right. The code below shows a way you could prevent this error from occurring. If no cells are found the rngSpecialCells keeps its original Nothing value and here this routes to the Exit. Depending on your requirements you may want a more sophisticated error handling.
Count
To obtain the number of cells, given a returned rngSpecialCells — not Nothing:
Looping over the cells
Along the same path, iterating over the cells in the range:
Different types of cells you can obtain
In this section I show the different types of cells you can obtain using SpecialCells
Get Last cell in worksheet using SpecialCells
Quite often, we need to know the last cell of the worksheet, which is achieved using the following code.
| Notes |
|---|
|
Empty cells
To get the cells with no content (data constant or via formula) use SpecialCells with Type xlCellTypeBlanks. In the below example this returns $A$2,$C$2
Finding cells that contain data constants
The first example on this page showed the code that returns a range containing all cells that contain numeric constants. If you want only cells with only logical (TRUE/FALSE) values use Value:=xlLogical , for text only use the code below.
If you want to check for cells containing data of any type you can leave out the Value argument:
Finding cells that contain formulas
The code below returns all formulas that result in errors. Similar to the previous sections you can also restrict the result to cells that get numbers, text or logical values by setting the corresponding Value argument to xlNumbers , xlxlTextValues or xlLogical respectively
Cells that have Comments
The following code — created fully using the Code VBA add-in — prints all the Comment on the active worksheet to the Immediate window.
If you want to exclude cells — in rows or columns — that are hidden:
Find Cells with Conditional Formats
To find Cells having any Conditional Formats use the code below.
If you want to find Cells having the same Conditional Formats as a specific cell — here with the more meaningful name of the single cell range rngSameAsThisCell:
Find Cells with Validation
To find Cells having any Validation use the code below.
If you want to find Cells having the same Conditional Formats as a specific cell — here with the more meaningful name of the single cell range rngSameAsThisCell:
CODE VBA — AGORA Software BV Copyright 1997-2019
Глюк работы в UDF методов SpecialCells и FindNext
Прежде чем читать далее необходимо знать что такое функция пользователя(UDF) и как её создать. Узнать про это можно из статьи: Что такое функция пользователя(UDF)?
Если кратко, то UDF это Ваша собственная функция для вызова её с листа(как и остальные функции Excel). Пишется UDF на встроенном в Excel языке программирования Visual Basic for Applications. UDF способны дополнить и расширить и без того немалый перечень встроенных функций Excel, но есть у UDF и ограничения. Например, они не могут изменять значения других ячеек, форматы ячеек(с некоторыми недокументированными отступлениями), а так же выделять ячейки(методами Select, Application.GoTo и т.п.). Если с изменением значений и форматов ячеек и выделением все более-менее понятно, то некоторые ограничения кажутся больше невменяемыми, чем интуитивно понятными. О них и пойдет речь в статье.
И для детального разбора мы возьмем указанные в заголовке методы, как наиболее часто используемые и многим понятные.
SpecialCells
Для определения последней заполненной ячейки на листе часто используется метод SpecialCells(читать подробнее про определение последней строки). Но он может быть использован и для определения только тех ячеек, которые содержат примечания, проверки данных, только пустые ячейки или только видимые и т.д. И это предоставляет разработчику VBA очень неплохой инструмент для быстрого отбора нужных ячеек. Но у этого метода есть свои недостатки. Например, он не работает на защищенных листах(если конечно, мы не применили трюк с защитой только от пользователя, но не от макроса). Хотя в случае защищенного листа VBA честно скажет сообщением об ошибке в момент выполнения. Однако, при использовании метода SpecialCells именно из UDF — VBA не выдаст никакой ошибки, а вернет результат. Правда, не тот, который ожидался. Возьмем код ниже:
Function UDF_SpecCells_LastCell() Dim rr As Range Set rr = Cells.SpecialCells(xlCellTypeLastCell) UDF_SpecCells_LastCell = rr.Address End Function
Если выполнить эту функцию напрямую из VBA, то UDF_SpecCells_LastCell вернет корректный адрес одной конкретной ячейки — последней(путь это будет $X$34 ). Но если выполнить эту функцию, записав в любую ячейку листа =UDF_SpecCells_LastCell() , то функция вернет адрес ячеек всего листа — $1:$1048576 . При этом даже защита листа в этом случае не будет помехой. Все потому, что сам метод SpecialCells по факту даже не выполняется, а просто игнорируется и итогом будет адрес родительского объекта — Cells.
Как же из UDF получить адрес последней ячейки?
Адрес последней ячейки можно узнать и другим способом, который точно не даст осечек — можно использовать объект UsedRange:
Function UDF_LastCell() Dim lr As Long, lc As Long With Application.Caller.Parent ‘обращаемся к листу, с которого вызвана функция ‘номер последней строки lr = .UsedRange.Row + .UsedRange.Rows.Count — 1 ‘номер последнего столбца lc = .UsedRange.Column + .UsedRange.Columns.Count — 1 End With ‘собираем из номера строки и столбца адрес UDF_LastCell = Cells(lr, lc).Address End Function
Однако, чтобы заменить другие возможности метода SpecialCells для работы в UDF , придется подойти индивидуально к каждой задаче. Например, для получения диапазона ячеек с примечаниями, можно использовать такой код:
‘————————————————————————————— ‘ Author : The_Prist(Щербаков Дмитрий) ‘ Профессиональная разработка приложений для MS Office любой сложности ‘ Проведение тренингов по MS Excel ‘ https://www.excel-vba.ru ‘ info@excel-vba.ru ‘ Purpose: UDF_GetCommentCells ‘ Функция возвращает адрес ячеек, содержащих комментарии ‘ rr — необязательный. Ссылка на диапазон, в котором надо найти примечания ‘ если не указан — берутся все ячейки листа ‘————————————————————————————— Function UDF_GetCommentCells(Optional rr As Range) Dim rAll As Range, rc As Range, rCmnts As Range If rr Is Nothing Then Set rAll = Application.Caller.Parent.UsedRange Else Set rAll = rr End If For Each rc In rAll ‘если в ячейке есть примечание If Not rc.Comment Is Nothing Then ‘собираем все ячейки в один диапазон If rCmnts Is Nothing Then Set rCmnts = rc Else Set rCmnts = Union(rCmnts, rc) End If End If Next UDF_GetCommentCells = rCmnts.Address End Function
Собственно, такой подход можно применять для поиска и других типов ячеек. Для только видимых надо будет применять проверку каждой на Rows.Hidden и Columns.Hidden, для получения только ячеек с формулами — hasFormula. Для получения ошибочных — IsError(Cell) и т.д. Но в любом случае это будет в разы медленнее, чем SpecialCells.
FindNExt
Еще один неплохой метод, с помощью которого можно искать адреса всех ячеек с определенным значением. Если зайти в справку по методу Find(он используется для поиска ячейки с определенным значением на листе), то там можно найти код поиска всех ячеек. Я его чуть модернизировал под работу внутри функции:
Function FindAllValues() Dim s As String, firstAddress As String, c As Range With Range(«E:E») Set c = .Find(2, LookIn:=xlValues, lookat:=xlWhole) If Not c Is Nothing Then firstAddress = c.Address Do s = s & c.Address & «; » Set c = .FindNext(c) If Not c Is Nothing Then If firstAddress = c.Address Then Exit Do End If End If Loop While Not c Is Nothing End If End With FindAllValues = s End Function
Чтобы проверить работу этой функции запишите в столбец E(начиная с ячейки E1) ряд значений(в каждую ячейку по одной цифре): 1, 2, 3, 2, 5, 4, 7.
И опять — если выполнить эту функцию напрямую из VBA, то FindAllValues вернет адреса всех ячеек с указанным значением(2) — $E$2; $E$4; . Но если выполнить эту функцию, записав в любую ячейку листа =FindAllValues() , то функция вернет адрес только первой найденной ячейки — $E$2; . Проблема в том, что метод FindNext не выполняется и возвращает Nothing .
Так же как и в случае со SpecialCells заменить такой поиск можно только собственными усилиями. Я для примера могу приложить такой вот не оптимальный, но рабочий код:
‘————————————————————————————— ‘ Author : The_Prist(Щербаков Дмитрий) ‘ Профессиональная разработка приложений для MS Office любой сложности ‘ Проведение тренингов по MS Excel ‘ https://www.excel-vba.ru ‘ info@excel-vba.ru ‘ Purpose: UDF_FindValCells ‘ Функция возвращает адрес ячеек, содержащих искомое значение ‘ v — искомое значение ‘ rr — необязательный. Ссылка на диапазон, в котором надо найти значения ‘ если не указан — берутся все ячейки листа ‘————————————————————————————— Function UDF_FindValCells(v, Optional rr As Range) Dim rAll As Range, rc As Range, rVals As Range If rr Is Nothing Then Set rAll = Application.Caller.Parent.UsedRange Else Set rAll = rr End If For Each rc In rAll ‘если значение в ячейке равно искомому If rc.Value = v Then ‘собираем все ячейки в один диапазон If rVals Is Nothing Then Set rVals = rc Else Set rVals = Union(rVals, rc) End If End If Next UDF_FindValCells = rVals.Address End Function
Быстрее поиск будет работать на массивах, но это уже другая история.
Еще несколько коварных методов
Так же проблемы возникнут с использованием таких методов как: CurrentRegion(определение всей прилегающей к активной ячейке таблицы. На листе можно вызывать нажатием сочетания клавиш Ctrl + A )), CurrentArray(определение всей области применения формулы массив), ShowPrecedents и ShowDependents(выделение зависимостей ячеек).
- CurrentRegion и CurrentArray при вызове из UDF всегда будут возвращать адрес активной ячейки
- ShowPrecedents и ShowDependents при вызове из UDF просто ничего не покажут
Аналогично игнорируются и все так называемые объекты окружения самого Excel. Например, методы изменения способа вычисления формул(Application.Calculation), изменение стиля ссылок(Application.ReferenceStyle), вид курсора(Application.Cursor) и многие другие. Т.е. получить текущее значение этих параметров можно, но вот изменить уже не получится.
Зато функцией UDF можно добавить в ячейку примечание. Функция ниже прекрасно отработает как при вызове непосредственно из VBA, так и при вызове с листа при помощи записи в ячейку функции =UDF_AddNewComment() :
Function UDF_AddNewComment() Dim rr As Range Set rr = ActiveCell ‘Application.Caller ‘если надо добавить в ячейку с самой UDF rr.AddComment («Привет от excel-vba.ru!») End Function
Если Вы так же обнаружите методы, которые некорректно работают при вызове из UDF — делитесь в комментариях, соберем коллекцию 🙂
Статья помогла? Поделись ссылкой с друзьями!
Range.SpecialCells: What does xlCellTypeBlanks actually represent?
The Range.SpecialCells method can be used to return a Range object meeting certain criteria. The type of criteria is specified using an xlCellType constant.
One of those constants (xlCellTypeBlanks) is described as referring to «Empty cells» with no further elaboration.
Does anyone know what definition of «Empty» this method uses? Does it include cells with no values/formulas but various other features (data validation, normal formatting, conditional formatting, etc)?
3 Answers 3
That type includes the subset of cells in a range that contain neither constants nor formulas. Say starting with an empty sheet we put something in A1 and A10 and then run:
Formatting and Comments are not included. Also note that all the «empty» cells below A10 are also ignored.
The definition does indeed contain the idea of having nothing in the cell, i.e. it excludes any cell that contains either:
- a numerical value
- a date or time value
- a text string (even an empty one)
- a formula (even if returning an empty string)
- an error
- a boolean value
But it also excludes any cell that’s not within the range going from A1 to the last used cell of the sheet (which can be identified programmatically through ws.cells.specialCells(xlCellTypeLastCell) , or by using the keyboard Ctrl+End ).
So if the sheet contains data down to cell C10 (i.e. Ctrl+End brings the focus to cell C10), then running Range(«D:D»).specialCells(xlCellTypeBlanks) will fail.
NB The range A1 to LastCellUsed can sometimes be different from the used range . That would happen if some rows at the top and/or some columns at on the left never contained any data.
On the other hand, cells that fit the empty definition above will be properly identified no matter any of the followings:
- size or colour of font
- background colour or pattern
- conditional formatting
- borders
- comments
- or any previous existence of these that would later have been cleared.
A bit beside the main subject, let me ask a tricky question related to how the term BLANK might be defined within Excel:
- How can a cell return the same value for CountA and CountBlank ?
Well, if a cell contains ‘ (which will be displayed as a blank cell), both CountA and CountBlank will return the value 1 when applied to that cell. My guess is that technically, it does contain something, though it is displayed as a blank cell. This strange feature has been discussed here.
In this example, both lines 6 & 7 will return 1 for both CountA and CountBlank.
So the term Blank doesn’t appear to be defined a unique way within Excel: it varies from tool to tool.
SpecialCells in Excel to return value of a formula
I wanted a quick simple way to copy cell values to another sheet using SpecialCells in Excel as opposed to looping
My VBA code is as below:
My source data Z4:Z20000 has formulas that returns a value (texts/numbers/fraction etc) or blank «». I want the copy to ignore the blanks, but copy any other value returned
The VBA code above using SpecialCells(xlCellTypeConstants) doesn’t work because of the formula in the source range.
My question: Is there a straightforward way I can use range.specialcells to copy my data from a worksheet to another bearing in mind that source cells contain formulas and the formulas may produce empty string cells which will need to be skipped
Создан 14 окт. 16 2016-10-14 10:06:05 yinka
This post is a possible duplicate of [Excel VBA Copy Range and Paste Values in another Sheet’s specific Range](http://stackoverflow.com/questions/21648122/excel-vba-copy-range-and-paste-values-in-another-sheets-specific-range) (since you have the range already correctly determined the range with SpecialCells). – Ralph 14 окт. 16 2016-10-14 10:13:44
@ralph this is not a duplicate of that in this case — not about copy-paste special, but range.specialcells. – vacip 14 окт. 16 2016-10-14 10:16:05
@vacip Depends whether I understand the question correctly or you did. I am reading from this post that the range is correctly determined. Yet, formulas are copied over and yinka wants to copy values only. You are reading that the range is not correctly determined. But the copy / paste works correctly. I just read through the post again and it I am still not sure if you got the OP right or if I understood the OP correctly. Yet, it seems that Ron Rosenfeld is sharing my thought process. I guess the OP will have to clarify. – Ralph 14 окт. 16 2016-10-14 10:20:24
2 ответа
If you have formulas, why are you trying to select the constants?
The 23 means «Numbers, Texts, Logicals and Errors».
Doing the copy and paste separately ensure blanks are skipped (if that’s what you mean by «ignore»).
Paste values makes sure only the values get pasted, not the formulas themselves.
Please note that if you have a formula in a cell, it is not blank. Even if the formula produces an empty string value as a result, the cell itself is not empty! In htat case, you need to do a copy-paste values in place before you do anything else — and even then Excel sometimes doesn’t consider blank cells blank. If this is the case, you need to iterate (loop) through the cells, and copy them one-by-one.
Создан 14 окт. 16 2016-10-14 10:14:39 vacip
To just paste the values, use the ‘.Copy’ method on that selection; then ‘.PasteSpecial(xlPasteValues)’ – Ron Rosenfeld 14 окт. 16 2016-10-14 10:59:54
@vacip This copied it but didn’t ignore blanks – yinka 14 окт. 16 2016-10-14 13:16:59
@yinka It is very very hard to understand what you are asking for. And I have a feeling you only provide 10% of the problem, so my solution will bump into another, unmentioned problem in your worksheet. Anyway, see my updated answer. – vacip 14 окт. 16 2016-10-14 13:55:47
@vacip I edited my question, but from your latest edit, it looks like I’ll have to loop. Thanks – yinka 14 окт. 16 2016-10-14 14:17:28
The easiest way I can think of is to remove the blanks after copying all:
The more complicated way is with array formula, but doesn’t need VBA.
Создан 14 окт. 16 2016-10-14 14:18:51 Slai