Microsoft Excel:

  Таблицы и VBA. Справочник.
  Вопросы и Ответы. Советы. Примеры.
Меню FAQ | Макросы | Range (Cells)


Rambler's Top100


Counter CO.KZ


    [1] [2] [3] [4] [5]

  1. Как обратится к ячейке (плюс несколько нестандартных методов выделения ячейки) ? 2004
  2. Как скрыть и отобразить конкретные строки или столбец ? 21.03.2005
  3. Как добавить целую строку или столбец ? 23.04.2014
  4. Как добавить строку или столбец в "умную" таблицу (список) ? NEW 09.09.2016
  5. Как выделить ячейки, расположенные с определённым интервалом ? 04.04.2005
  6. Как выделить ячейку неактивного рабочего листа, без использования методов .Select или .Activate ? Лидер голосования 12.01.2006
  7. Как выделить несмежные ячейки, диапазоны ? 16.03.2006
  8. Как определить количество несмежных ячеек/диапазонов в выделенном диапазоне ? 24.10.2006
  9. Как в XL95, XL97 получить смещение относительно несмежных ячеек/диапазонов ? 27.06.2010
  10. Как определить является ли выделенный об'ект ячейкой/диапазоном ? 12.07.2006
  11. Как суммировать данные из диапазона ячеек ? 2004
  12. Как отобразить содержимое ячейки с учётом формата ячейки ? 05.05.2005
  13. Как определить адрес выделенного диапазона ? 12.07.2006
  14. Как определить адрес видимого диапазона ? 29.07.2006
  15. Как определить адрес активной ячейки ? 2004
  16. Как определить адрес первой и последней ячейки диапазона смежных ячеек ? 21.12.2006
  17. Как определить диапазон листа с данными ? 2004
  18. Как определить последнюю заполненную строку и столбец ? 2004
  19. Как определить буквенное обозначение столбца ? 06.03.2006
  20. Как определить пустая или нет ячейка ? 19.07.2006
  21. Как определить пустая или нет строка, столбец ? 22.12.2005
  22. Как подсчитать количество пустых ячеек в указанном диапазоне ? 21.02.2009
  23. Как подсчитать количество ячеек, содержащих числа, а также текст ? 10.10.2010
  24. Как определить количество ячеек в диапазоне ? 10.10.2010
  25. Как определить пересекаются или нет диапазоны ячеек ? 06.06.2006
  26. Как определить соответствие ячейки заданному диапазону ? 06.06.2006
  27. Как определить является ли ячейка частью "умной" таблицы (списка) ? NEW 24.08.2016
  28. Как проверить защищёна или нет ячейка рабочего листа (диапазон) ? 28.01.2007
  29. Как конвертировать адрес ячейки/диапазона из стиля A1 в R1C1 и наоборот ? 25.04.2014
  30. Как с помощью диалогового окна выделить ячейки и получить адрес выбранного диапазона ? 20.12.2005
  31. Как удалить ячейку со сдвигом влево или вверх ? 20.04.2007
  32. Как об'единить ячейки ? 14.07.2006
  33. Как об'единить ячейки по строкам ? 14.07.2006
  34. Как об'единить ячейки по столбцам ? 28.05.2014
  35. Как об'единить ячейки и данные всех этих ячеек
    (т.е. об'единить ячейки без потери данных) ?
    01.05.2008
  36. Как определить адрес всех об'единённых ячеек, исходя из любой ячейки этого диапазона ? 30.09.2006
    [1] [2] [3] [4] [5]


  • Ответ :
  • Range("B5").Select
    Range("$B$5").Select
    Range("Дата").Select
    Range("B" & 5).Select

    [B5].Select
    [Дата].Select

    Range("A5").Next.Select
    Range("C5").Previous.Select

    Range("A5").Range("B1").Select
    Range("B1").Range("A5").Select

    Application.Goto Range("B5")
    Application.Goto Reference:=Range("B5")
    Application.Goto Reference:=Range("Дата")
    Application.Goto "Дата"
    Application.Goto [Дата]
    Application.Goto Reference:="Дата"
    Application.Goto Reference:=[Дата]

    Range(Names!Дата).Select
    Range(ThisWorkbook.Names!Дата).Select
    Range(Application.Names!Дата).Select

    Range(Names("Дата")).Select
    Range(ThisWorkbook.Names("Дата")).Select
    Range(Application.Names("Дата")).Select

    Names!Дата.RefersToRange.Select
    ThisWorkbook.Names!Дата.RefersToRange.Select
    Application.Names!Дата.RefersToRange.Select

    Names("Дата").RefersToRange.Select
    ThisWorkbook.Names("Дата").RefersToRange.Select
    Application.Names("Дата").RefersToRange.Select

    Range("B5:B10").Item(1).Select
    Range("B5:B10").Item(1, 1).Select
    Range("A5:CM5").Item(1, 2).Select
    Range("B1:B10").Item(5).Select
    Range("B1:B10").Item(5, 1).Select
    Range("C1:D10").Item(5, 0).Select
    Range("D1:H50").Item(5, -1).Select
    Range("A17:E15").Item(-9, 2).Select
    Range("D10:E15").Item(-4, -1).Select

    Range("B5:B10")(1).Select
    Range("B5:B10")(1, 1).Select
    Range("D1:H50")(5, -1).Select
    Range("A17:E15")(-9, 2).Select
    Range("D10:E15")(-4, -1).Select

    Range("B5:B10").Cells(1).Select
    Range("B5:B10").Cells(1, 1).Select
    Range("A5:CM5").Cells(1, 2).Select
    Range("B1:B10").Cells(5).Select
    Range("B1:B10").Cells(5, 1).Select
    Range("C1:D10").Cells(5, 0).Select
    Range("D1:H50").Cells(5, -1).Select
    Range("A17:E15").Cells(-9, 2).Select
    Range("D10:E15").Cells(-4, -1).Select

    Range("B:B").Item(5).Select
    Range("B:B").Cells(5).Select
    Range("B:B").Rows(5).Select
    Range("B:B").Rows("5:5").Select

    Range("5:5").Item(2).Select
    Range("5:5").Cells(2).Select
    Range("5:5").Columns(2).Select
    Range("5:5").Columns("B").Select
    Range("5:5").Columns("B:B").Select

    Range("B:B 5:5").Select
    Range("B:B" & " " & "5:5").Select

    Range("B1").Offset(4).Select
    Range("C1").Offset(4, -1).Select
    Range("C10").Offset(-5, -1).Select

    Range("B5,B5").Select

    [B5:B5].Select
    [B5.B5].Select
    Range("B5.B5").Select
    Range("B5:B5").Select
    Range("B5:B5:B5").Select
    Range("B5", "B5").Select
    Range("$B$5", "$B$5").Select
    Range("B5", Range("B5")).Select
    Range(Range("B5"), "B5").Select
    Range(Range("B5"), Range("B5")).Select

    Range(Cells(1026), Cells(1026)).Select
    Range(Cells(5, 2), Cells(5, 2)).Select
    Range(Cells(5, "B"), Cells(5, "B")).Select
    Range(Cells(5, "B:B"), Cells(5, "B:B")).Select

    Cells(5, 2).Select
    Cells(5, "B").Select
    Cells(5, "B:B").Select
    Cells(1026).Select

    Rows(5).Cells(2).Select
    Rows(5).Columns(2).Select
    Rows(5).Columns("B").Select
    Rows(5).Columns("B:B").Select
    Rows("5:5").Columns("B").Select
    Rows("5:5").Columns("B:B").Select

    Columns(2).Cells(5).Select
    Columns(2).Rows(5).Select
    Columns(2).Rows("5:5").Select
    Columns("B").Cells(5).Select
    Columns("B").Rows(5).Select
    Columns("B").Rows("5:5").Select
    Columns("B:B").Cells(5).Select
    Columns("B:B").Rows(5).Select
    Columns("B:B").Rows("5:5").Select
    Примечание :
  • - Во всех примерах выделяется ячейка "B5"
  • - Для реализации примера № 3, 6, 13-29 ячейка должна быть именованной (Дата)
    [Как присвоить имя ячейке средствами VBA]
    [Как присвоить имя ячейке стандартными средствами]
  • - Пример № 67 нужно использовать для работы с несмежными ячейками и/или диапазоном.
  • - Примеры № 68-81 нужно использовать для работы с диапазоном ячеек.

    * - Автор примеров № 18-23 - Steven Roman
    ** - Автор примера № 69 - Richard Shepherd

    Комментарий : Выделение ячейки используется только для наглядности, в действительности же, реальная необходимость в активации и выделении, возникает крайне редко.
  • Ответ :

  • Если известен номер строки, столбца или его буквенное обозначение, то :
  • Rows(2).Hidden = True
    Rows("3:4").Hidden = True

    Columns(2).Hidden = True
    Columns("C").Hidden = True
    Columns("E:F").Hidden = True
  • Если известен только адрес ячейки, то :
  • Range("A2").EntireRow.Hidden = True
    Range("B3:D4").EntireRow.Hidden = True

    Range("C3").EntireColumn.Hidden = True
    Range("E2:F5").EntireColumn.Hidden = True
  • Для того, чтобы отобразить строку или столбец, нужно изменить значение с True на False
  • Ответ :

  • Для того, чтобы добавить целую строку/столбец достаточно использовать метод Insert об'екта Range, т.е. если нам известен номер строки, столбца или его буквенное обозначение, то :
  • Rows(5).Insert
    Range("5:5").Insert

    Columns(3).Insert
    Columns("C").Insert
    Range("C:C").Insert
  • Если известен только адрес ячейки, то :
  • Range("A5").EntireRow.Insert

    Range("F1").EntireColumn.Insert
    Если же возникнет необходимость в добавлении нескольких строк/столбцов, то в таком случае можно воспользоваться следующим вариантом :
    Rows("5:7").Insert
    Range("5:7").Insert

    Columns("C:D").Insert
    Range("C:D").Insert
    К сожалению, на практике чаще требуется добавить несколько строк/столбцов относительно некой ячейки, поэтому рекомендую запомнить такой синтаксис, где номер строки и столбца исходной ячейки, а также количество добавляемых строк/столбцов - удобно указывать в виде переменной :
    Cells(5, 2).EntireRow.Resize(3).Insert

    Cells(10, 5).EntireColumn.Resize(, 3).Insert
    Обратите внимание на то, что применение свойства Resize, применительно к ячейкам несмежного диапазона, вызовет ошибку, которой можно избежать, если просто использовать цикл.
  • Ответ :

    Для того, чтобы добавить строку или столбец в "умную" таблицу (список) достаточно использовать метод Add об'ектов ListRows и ListColumns соответственно.
  • ActiveSheet.ListObjects(1).ListRows.Add
    ActiveSheet.ListObjects(1).ListColumns.Add
    Примечание : Обратите внимание на две вещи. Во-первых, этот метод возвращает об'ект Range, т.е. Вы можете сразу использовать новую строку/столбец. Во-вторых, этот метод имеет один необязательный аргумент Position, использование которого позволяет указать позицию новой строки/столбца. Например, если указать 1, то новая строка/столбец станет первой, а если указать несуществующую позицию *, то возникнет ошибка. * Position - это число от 1 до кол-во строк/столбцов в "умной" таблице (списке)
  • Ответ :
  • iFirstRow = 3: iLastRow = 300

    For iRow = iFirstRow To iLastRow Step 3

    Set iCellOne = Cells(iRow, 1) ' Столбец "A"

    If iRow = iFirstRow Then
       Set iCellTwo = Union(iCellOne, iCellOne)
    Else
       Set iCellTwo = Union(iCellOne, iCellTwo)
    End If

    Next

    iCellTwo.Select
    Управляющую инструкцию If Then Else можно заменить на функцию IIf :
    iFirstRow = 3: iLastRow = 300

    For iRow = iFirstRow To iLastRow Step 3

    Set iCellOne = Cells(iRow, 1) ' Столбец "A"
    Set iCellTwo = Union(iCellOne, _
        IIf(iRow = iFirstRow, iCellOne, iCellTwo))

    Next

    iCellTwo.Select
    Иногда, можно обойтись без проверки, например :
    iFirstRow = 3: iLastRow = 300

    Set iCells = Cells(iFirstRow, 1) ' Столбец "A"

    For iRow = iFirstRow To iLastRow Step 3
        Set iCells = Union(iCells, Cells(iRow, 1))
    Next

    iCells.Select


    Set iCells = Cells(3, 1) ' Столбец "A"

    For iRow = 6 To 300 Step 3
        Set iCells = Union(iCells, Cells(iRow, 1))
    Next

    iCells.Select
    Комментарий : Выделение ячеек используется только для наглядности, в действительности же, реальная необходимость в активации и выделении, возникает весьма редко.
  • Ответ : Вопрос выбран посетителями
  • Application.Goto Reference:=Worksheets(3).Range("A1")
    Application.Goto Reference:=Worksheets("Manager").Range("A1")
    Используя этот метод, мы также можем выделить диапазон ячеек, в том числе и несмежных :
    Application.Goto Reference:=Worksheets(3).Range("A1:C10")
    Application.Goto Reference:=Worksheets(3).Range("A1,C3,D5:F10")
    Application.Goto Reference:=Worksheets("Manager").Range("A1:C10")
    Application.Goto Reference:=Worksheets("Manager").Range("A1,C3,D5:F10")
    Предполагается, что :
  • 3 - это индекс рабочего листа
  • Manager - это имя рабочего листа

    Также допускается выделение ячеек неактивной рабочей книги :
  • Application.Goto _
    Reference:=Workbooks("Report.xls").Worksheets("Sales").Range("M13")
    Application.Goto _
    Reference:=Workbooks("Report.xls").Worksheets(1).Range("M13")
    Application.Goto _
    Reference:=Workbooks(2).Worksheets("Sales").Range("M13")
    Application.Goto _
    Reference:=Workbooks(2).Worksheets(1).Range("M13")
    Предполагается, что :
  • 2 - это индекс открытой рабочей книги
  • Report.xls - это имя открытой рабочей книги
  • 1 - это индекс рабочего листа
  • Sales - это имя рабочего листа

    Также допускается выделение именованных ячеек и диапазонов :
  • Application.Goto Reference:=Range("Дата")
    Application.Goto "Дата"
    Application.Goto [Дата]
    Application.Goto Reference:="Дата"
    Application.Goto Reference:=[Дата]
    Предполагается, что :
  • Дата - это имя ячейки или диапазона ячеек уровня рабочей книги

    Для выделения именованных ячеек уровня рабочего листа, необходимо : либо добавить ссылку на рабочий лист, либо указать полное имя
  • Application.Goto _
    Reference:=Worksheets(3).Range("Дата")
    Application.Goto _
    Reference:=Worksheets("Manager").Range("Дата")

    Application.Goto Reference:=Range("Manager!Дата")
    Application.Goto [Manager!Дата]
    Application.Goto Reference:="Manager!Дата"
    Для выделения именованных ячеек неактивной рабочей книги, нужно добавить ссылку на эту книгу.
    Совет : Используя свойство .PreviousSelections можно узнать диапазон, который был выделен перед применением метода .GoTo
    With Application
         If IsArray(.PreviousSelections) = True Then
            For Each iSelection In .PreviousSelections
                MsgBox iSelection.Address(External:=True)
            Next
         End If
    End With

  • Ответ :

    Вариант I.
  • Range("A1,C5:D10,H15").Select
    Вариант II.
    Union(Range("A1"), Range("C5:D10"), Range("H15")).Select
    Комментарий : Выделение несмежных диапазонов используется исключительно для наглядности, в действительности же, реальная необходимость в активации и выделении, возникает крайне редко.
  • Ответ : Скачать пример

    Вариант I.
  • iCountAreas = Selection.Areas.Count
    MsgBox "Количество несмежных ячеек/диапазонов - " & iCountAreas
    Вариант II.
    iCountAreas = ExecuteExcel4Macro("AREAS(SELECTION())")
    MsgBox "Количество несмежных ячеек/диапазонов - " & iCountAreas
    Примечание : Не забудьте предварительно проверить является ли выделенный об'ект диапазоном [см. ниже]

    Комментарий : Используя первый способ можно определить количество несмежных ячеек/диапазонов в любом диапазоне, при этом, выделять его специально, конечно же, необязательно.
  • Ответ :

    Вариант I.
  • If TypeOf Selection Is Range Then
       MsgBox "Выделен именно об'ект ""Range"""
    Else
       MsgBox "Выделен : " & TypeName(Selection)
    End If
    Вариант II.
    If TypeName(Selection) = "Range" Then
       MsgBox "Выделен именно об'ект ""Range"""
    Else
       MsgBox "Выделен : " & TypeName(Selection)
    End If

  • Ответ :
  • For iRow = 2 To 12
        iSum = iSum + Cells(iRow, 1)
    Next

    MsgBox "Сумма ячеек :" & iSum
    Предполагается, что :
  • 1 - это столбец "A"
  • iRow - это строки с 2 по 12
  • iSum - это Сумма ячеек

    Примечание : Если в любой ячейке будут данные отличные от числовых, то Вы получите ошибку. Однако об'единив этот пример с одним из предыдущих вопросов можно написать следующий код :
  • For iRow = 2 To 12
    iData = Cells(iRow, "A") 'Cells(iRow, 1)
        If IsNumeric(iData) = True Then
           iSum = iSum + iData
        End If
    Next
    MsgBox "Сумма ячеек :" & iSum
    Предполагается, что :
  • "A" - это столбец "A"
  • iRow - это строки с 2 по 12
  • iData - это данные из ячейки
  • iSum - это Сумма ячеек
    Данный способ подсчёта суммы почти незаменим, если необходимо задать несколько условий проверки :
  • If IsNumeric(iData) = True And Cells(iRow, 3) = "долг" Then

  • Ответ : Скачать пример
  • iRangeText = ActiveCell.Text

    iRangeText = Cells(1).Text
    iRangeText = Range("A1").Text ' и т.д.
    Примечание : Если существует вероятность, что ширина столбца может повлиять на корректность отображения данных, то в этом случае нужно :
    With Range("A1")
         If Not IsError(.Value) Then
            MsgBox Application.Text(.Value, .NumberFormat)
         Else
            MsgBox "Ячейка содержит значение ошибки"
         End If
    End With
    Комментарий : В некоторых случаях * результаты могут различаться.
  • Ответ :

    Вариант I-IV.
  • iSelectionAddress = Selection.Address
    iSelectionAddress = Excel.Selection.Address
    iSelectionAddress = Application.Selection.Address
    iSelectionAddress = ActiveWindow.Selection.Address
    MsgBox iSelectionAddress
    Примечание : Если в момент исполнения будет выделен графический об'ект, то Вы получите ошибку, которую можно избежать, если использовать соответствующую проверку или следующий вариант.

    Вариант V.
    iSelectionAddress = ActiveWindow.RangeSelection.Address
    MsgBox iSelectionAddress
    Комментарий : Отличие RangeSelection от Selection заключается в том, что при выделенных графических об'ектах это свойство возвращает диапазон, который был выделен перед выделением графического об'екта.
  • Ответ :
  • iVisibleRange = ActiveWindow.VisibleRange.Address
    iVisibleRange = Excel.ActiveWindow.VisibleRange.Address
    iVisibleRange = Application.ActiveWindow.VisibleRange.Address

    MsgBox "Сейчас виден, в т.ч. и частично, диапазон : " & iVisibleRange
    Комментарий : Если активное окно было разделено или имеет закреплённые области (меню Окно), то вышеприведённое свойство будет применяться только к последней области. Если это недопустимо, то получить весь видимый диапазон можно с помощью нижеопубликованного кода, который, кстати, является универсальным, т.к. его можно использовать в обоих случаях.
    Dim iScreen As Range, iCount%

    With ActiveWindow.Panes
         Set iScreen = .Item(1).VisibleRange
         For iCount = 2 To .Count
             Set iScreen = Union(iScreen, .Item(iCount).VisibleRange)
         Next
    End With

    MsgBox "Сейчас виден, в т.ч. и частично, диапазон : " & iScreen.Address

  • Ответ :
  • iAddress = ActiveCell.Address
    MsgBox iAddress
    Для того чтобы убрать значение абсолютной ссылки $A$1 на строку и столбец нужно :
    iAddress = ActiveCell.Address(RowAbsolute:=False, ColumnAbsolute:=False)

  • Ответ :

    Вариант I.
  • With Range("F5:J153")
         iF_Address = .Item(1).Address
         iL_Address = .Item(.Count).Address
    End With
    MsgBox "Адрес первой ячейки : " & iF_Address & _
    vbCrLf & "Адрес последней ячейки : " & iL_Address
    Вариант II.
    With Range("B3:Q742")
         iF_Address = .Cells(1).Address
         iL_Address = .Cells(.Count).Address
    End With
    MsgBox "Адрес первой ячейки : " & iF_Address & _
    vbCrLf & "Адрес последней ячейки : " & iL_Address

  • Ответ :
  • iUsedRangeAddress = ActiveSheet.UsedRange.Address
    MsgBox iUsedRangeAddress

    Set iUsedRange = ActiveSheet.UsedRange
    MsgBox iUsedRange.Address

  • Ответ :
  • With ActiveSheet.UsedRange
         iRow = .Row + .Rows.Count - 1
         iClm = .Column + .Columns.Count - 1
    End With
    With Worksheets(1).UsedRange
         iRow = .Row + .Rows.Count - 1
         iClm = .Column + .Columns.Count - 1
    End With
    With Worksheets("Лист1").UsedRange
         iRow = .Row + .Rows.Count - 1
         iClm = .Column + .Columns.Count - 1
    End With
    Существуют и другие способы определения последней заполненной ячейки [Подробнее]

    Обратите внимание на следующие инструкции, которые помогут определить количество строк и столбцов в об'екте UsedRange
    iCountRow = ActiveSheet.UsedRange.Rows.Count
    iCountClm = ActiveSheet.UsedRange.Columns.Count

  • Ответ :

    Вариант I, II, III (исходя из адреса ячейки)
  • iClmIndex = ActiveCell.Column
    iAddress = ActiveCell.Address

    iColumn = Mid(iAddress, 2, IIf(iClmIndex > 26, 2, 1))
    iClmIndex = ActiveCell.Column
    iAddress = ActiveCell.Address(RowAbsolute:=False, ColumnAbsolute:=False)

    iColumn = Left(iAddress, IIf(iClmIndex > 26, 2, 1))
    iAddress = ActiveCell.EntireColumn.Address(ColumnAbsolute:=False)
    iPosition = InStr(iAddress, ":") - 1

    iColumn = Left(iAddress, iPosition)
    Вариант IV. (исходя из номера столбца)
    iClmIndex = ActiveCell.Column

    Select Case iClmIndex
    Case 1 To 26
         iColumn = Chr(64 + iClmIndex)
    Case 27 To 256
         iColumn = Chr(64 + (iClmIndex \ 26)) & _
         Chr(64 + iClmIndex - (iClmIndex \ 26) * 26)
    End Select
    iClmIndex = ActiveCell.Column

    If iClmIndex < 27 Then
       iColumn = Chr(64 + iClmIndex)
    Else
       iColumn = Chr(64 + (iClmIndex \ 26)) & _
       Chr(64 + iClmIndex - (iClmIndex \ 26) * 26)
    End If
    Примечание :
  • - Активная ячейка использована исключительно в качестве примера
  • Ответ :

    Вариант I, II, III
  • If IsEmpty(Range("A1")) = True Then
       MsgBox "Ячейка пустая"
    Else
       MsgBox "Ячейка непустая"
    End If
    If VarType(Range("A1")) = vbEmpty Then
       MsgBox "Ячейка пустая"
    Else
       MsgBox "Ячейка непустая"
    End If
    If TypeName(Range("A1").Value) = "Empty" Then
       MsgBox "Ячейка пустая"
    Else
       MsgBox "Ячейка непустая"
    End If
    Совет : Если Вы хотите получить корректный результат проверки, то не используйте сравнение, типа = "" или = Empty, так как ячейка может содержать, например, формулу которая возвращает ""
    Сравнение же с пустой строкой <> "" можно использовать, если Вам необходимо проверить содержит ли ячейка значение, отличное от ""

  • Ответ :

    Вариант I.
  • iText = Rows(3).Text
    If iText = "" Then
       MsgBox "Строка пустая"
    Else
       MsgBox "Строка непустая"
    End If
    Вариант II.
    iCount = Application.CountA(Rows(5))
    If iCount = 0 Then
       MsgBox "Строка пустая"
    Else
       MsgBox "Строка непустая"
    End If
    iCount = Application.CountA(Columns(2))
    If iCount = 0 Then
       MsgBox "Столбец пустой"
    Else
       MsgBox "Столбец непустой"
    End If
    With Worksheets(1).Rows(10)
         iCount = Application.CountA(.Cells)
         If iCount = 0 Then
            MsgBox "Строка № " & .Row & " пустая"
         Else
            MsgBox "Строка № " & .Row & " непустая"
         End If
    End With
    With Worksheets(1).Columns(3)
         iCount = Application.CountA(.Cells)
         If iCount = 0 Then
            MsgBox "Столбец № " & .Column & " пустой"
         Else
            MsgBox "Столбец № " & .Column & " непустой"
         End If
    End With
    Вариант III.
    With Worksheets(1).Rows(1)
         iCount = Application.CountBlank(.Cells)
         If iCount = .Cells.Count Then
            MsgBox "Строка № " & .Row & " пустая"
         Else
            MsgBox "Строка № " & .Row & " непустая"
         End If
    End With
    With Worksheets(1).Columns(4)
         iCount = Application.CountBlank(.Cells)
         If iCount = .Cells.Count Then
            MsgBox "Столбец № " & .Column & " пустой"
         Else
            MsgBox "Столбец № " & .Column & " непустой"
         End If
    End With
    Вариант IV.
    If Rows(4).Find(What:="*") Is Nothing Then
       MsgBox "Строка пустая"
    Else
       MsgBox "Строка непустая"
    End If
    If Columns(1).Find(What:="*") Is Nothing Then
       MsgBox "Столбец пустой"
    Else
       MsgBox "Столбец непустой"
    End If
    Вариант V.
    On Error Resume Next

    iCountFormulas = Rows(20).SpecialCells(xlFormulas).Count
    iCountConstants = Rows(20).SpecialCells(xlConstants).Count
    iCountCells = iCountFormulas + iCountConstants

    If iCountCells = 0 Then
       MsgBox "Строка пустая"
    Else
       MsgBox "Строка непустая"
    Еnd If
    On Error Resume Next

    iCountFormulas = Columns(10).SpecialCells(xlFormulas).Count
    iCountConstants = Columns(10).SpecialCells(xlConstants).Count
    iCountCells = iCountFormulas + iCountConstants

    If iCountCells = 0 Then
       MsgBox "Столбец пустой"
    Else
       MsgBox "Столбец непустой"
    End If
    Совет :
    - Второй вариант является наиболее "универсальным"
    - Вместо строки/столбца можно указать конкретный диапазон ячеек, см. пример ...
    iCount = Application.CountA(Range("A1:E5"))
    If iCount = 0 Then
       MsgBox "Диапазон не содержит данных"
    Else
       MsgBox "Диапазон содержит данные"
    End If
    iCount = Application.CountA(Range("A1:E5"))

    MsgBox "Диапазон " & IIf(iCount = 0, "не ", "") & "содержит данные"

  • Ответ :

    Для того, чтобы программно подсчитать количество пустых ячеек в определённом диапазоне, можно воспользоваться нижеопубликованной функцией. При этом желательно учесть, что в отличии от формул, эта функция может быть использована при работе с несмежным диапазоном, кроме того, данную функцию можно использовать и как аналог стандартной функции рабочего листа =СЧИТАТЬПУСТОТЫ(), которая считает ячейки, содержащие апостроф ' или пустую строку "" пустыми.
  • Public Function CountEmpty&( _
        iDiapazon As Range, Optional iAnalog As Boolean)
    
        If Not iAnalog Then
           CountEmpty = _
           iDiapazon.Count - Application.CountA(iDiapazon)
        Else
           Dim iArea As Range
           For Each iArea In iDiapazon.Areas
               CountEmpty = _
               CountEmpty + Application.CountBlank(iArea)
           Next
        End If
    
    End Function
    Пример вызова вышеопубликованной авторской функции из макроса :
    Private Sub Test()
    
        'CountEmpty
        
        MsgBox CountEmpty([A1:C3,C6:G8]) '[...], False
        MsgBox CountEmpty(Range("A1:C3,C6:G8"))
        MsgBox CountEmpty(Union([A1:C3], [C6:G8]))
        
        'CountBlank
        
        MsgBox CountEmpty([A1:C3,C6:G8], True)
    
    End Sub
    Пример вызова из ячеек рабочего листа :

    =CountEmpty((A1:C3;C6:G8))

    Замена функции =СЧИТАТЬПУСТОТЫ() :

    =CountEmpty((A1:C3;C6:G8);1)
    =CountEmpty((A1:C3;C6:G8);ИСТИНА)

  • Ответ :
  • If Not Intersect(Range("A5:B10"), Range("B3:F7")) Is Nothing Then
       MsgBox "Диапазоны пересекаются"
    Else
       MsgBox "Диапазоны не пересекаются"
    End If
    Получить диапазон, который находится на пересечении всех указанных диапазонов, можно так :
    Set iRangeInter = Intersect(Range("I5:N12"), Range("K1:K25"))

    If iRangeInter Is Nothing Then
       MsgBox "К сожалению, диапазоны не пересекаются"
    Else
       MsgBox "Адрес полученного диапазона : " & iRangeInter.Address
    End If

  • Ответ :

    Вариант I.
  • If Not Intersect(Range("C5"), Range("A1:V15")) Is Nothing Then
       MsgBox "Искомая ячейка входит в нужный диапазон"
    Else
       MsgBox "Искомая ячейка не входит в нужный диапазон"
    End If
    Вариант II.
    If Range("A1:V15").Address = _
       Range(Range("C5"), Range("A1:V15")).Address Then
       MsgBox "Искомая ячейка входит в нужный диапазон"
    Else
       MsgBox "Искомая ячейка не входит в нужный диапазон"
    End If
    Вариант III.
    With Range("A3:F15")
         If .Row <= Range("C5").Row And _
            .Row + .Rows.Count - 1 >= Range("C5").Row Then
            If .Column <= Range("C5").Column And _
               .Column + .Columns.Count - 1 >= Range("C5").Column Then
               MsgBox "Искомая ячейка входит в нужный диапазон"
               Exit Sub ' Exit Function
            End If
         End If
         MsgBox "Искомая ячейка не входит в нужный диапазон"
    End With
    Set iRange = Range("A3:F15")
    Set iCell = Range("C5")

    If iRange.Row <= iCell.Row And _
       iRange.Row + iRange.Rows.Count - 1 >= iCell.Row Then
       If iRange.Column <= iCell.Column And _
          iRange.Column + iRange.Columns.Count - 1 >= iCell.Column Then
          MsgBox "Искомая ячейка входит в нужный диапазон"
          Exit Sub ' Exit Function
       End If
    End If
    MsgBox "Искомая ячейка не входит в нужный диапазон"
    iResult = "Искомая ячейка не входит в нужный диапазон"

    With Range("A3:F15")
         If .Item(1).Row <= Range("C27").Row And _
            .Item(.Cells.Count).Row >= Range("C27").Row Then
            If .Item(1).Column <= Range("C27").Column And _
               .Item(.Cells.Count).Column >= Range("C27").Column Then _
               iResult = "Искомая ячейка входит в нужный диапазон"
         End If
    End With
    MsgBox iResult, vbExclamation, ""
    Совет : Аналогичным способом можно определить соответствие ячейки именованному диапазону, предварительно проверив принадлежат ли ячейка и диапазон одному рабочему листу.

  • Ответ : Актуально для MS Excel 2003 (и старше)
  • If Range("A1").ListObject Is Nothing Then
       MsgBox "Ячейка A1 не является частью умной таблицы"
    Else
       MsgBox "Ячейка A1 - часть умной таблицы"
    End If
    Если нужно не только определить соответствие, но и получить доступ к "умной" таблице(списку), то в такое случае можно применить такой способ. Разумеется, имя таблицы, а также её адрес, использованы только для наглядности.
    Dim iListObject As ListObject
    Set iListObject = Range("A1").ListObject

    If Not iListObject Is Nothing Then
       MsgBox "Ячейка A1 это часть " & _
       iListObject.Name, , iListObject.Range.Address
       'Здесь Вы можете работать с об'ектом iListObject
    Else
       MsgBox "Это просто ячейка A1"
    End If

  • Ответ : Скачать пример
  • With ThisWorkbook.Worksheets(1)
         If Not .ProtectContents Or Not .Range("A1").Locked Then
            MsgBox "Ячейка/Диапазон не защищены"
         Else
            MsgBox "Ячейка/Диапазон защищена"
         End If
    End With
    С диапазоном деле обстоит аналогичным образом, но если необходимо определить защищён ли диапазон в т.ч. и частично, то :
    With ThisWorkbook.Worksheets(1)
         If .ProtectContents = True Then
            Select Case .Range("A1:A10,B5").Locked
                Case True:  MsgBox "Диапазон защищён"
                Case False: MsgBox "Диапазон не защищён"
                Case Else:  MsgBox "Диапазон защищён частично"
            End Select
         Else
            MsgBox "Диапазон не защищён"
         End If
    End With
    With ThisWorkbook.Worksheets(1)
         If .ProtectContents = True Then
            iLocked = .Range("A1:A10,B5").Locked
            If IsNull(iLocked) = True Then
                MsgBox "Диапазон защищён частично"
            ElseIf iLocked = True Then
                MsgBox "Диапазон защищён"
            Else
                MsgBox "Диапазон не защищён"
            End If
         Else
            MsgBox "Диапазон не защищён"
         End If
    End With
    Примечание : Для программного изменения значений защищённых ячеек и диапазонов можно использовать этот [FAQ42]
  • Ответ :

    Если Вам необходимо конвертировать адрес ячейки/диапазона из стиля A1 в R1C1 и наоборот, то для этой цели идеально подойдёт метод ConvertFormula об'екта Application, т.е.
  • iAddressA1 = "$A$1" '"A1"

    iAddressR1C1 = Application.ConvertFormula(Formula:=iAddressA1, _
                   FromReferenceStyle:=xlA1, ToReferenceStyle:=xlR1C1)
    и собственно, наоборот
    iAddressR1C1 = "R10C5"

    iAddressA1 = Application.ConvertFormula(Formula:=iAddressR1C1, _
                 FromReferenceStyle:=xlR1C1, ToReferenceStyle:=xlA1)
    Комментарий : Обратите внимание на то, что с помощью данной функции Вы можете конвертировать не только адреса ячеек/диапазонов, но и формулы :
    iFormulaR1C1 = "=SUMIF(R1C1:R100C1,""*Климов*"",R1C2:R100C2)"

    iFormulaA1 = Application.ConvertFormula(Formula:=iFormulaR1C1, _
                 FromReferenceStyle:=xlR1C1, ToReferenceStyle:=xlA1)
    Другие примеры конвертации формул, можно найти в соответствующем разделе, а именно [FAQ679]

    Вариант II.

    Если же конвертация нужна только из стиля A1 в R1C1, и в момент выполнения макроса, активным будет лист имеющий ячейки, то для решения поставленной задачи можно "привлечь" свойство Address об'екта Range и его необязательный аргумент ReferenceStyle, т.е.
    iAddressA1 = "$C$10" '"C10"

    iAddressR1C1 = Range(iAddressA1).Address(ReferenceStyle:=xlR1C1)

  • Ответ :

    Вариант I. (метод InputBox)
  • iFormula = Application.InputBox( _
               Prompt:="Выделите ячейку или диапазон", _
               Title:="", Type:=0)
    
    If iFormula <> False Then
       iAddress = Mid(iFormula, 2, Len(iFormula))
       iAddress = Application.ConvertFormula( _
                  Formula:=iAddress, _
                  FromReferenceStyle:=xlR1C1, _
                  ToReferenceStyle:=xlA1)
    Else
       MsgBox "Вы забыли выделить нужные ячейки ..."
    End If
    Dim iAddress As Variant, iDiapazon As Range
    
    With Application
         iAddress = .InputBox("Выделите ячейку или диапазон", "", , , , , , 0)
         iAddress = .ConvertFormula(iAddress, xlR1C1, xlA1)
         If TypeName(Evaluate(iAddress)) = "Range" Then
            Set iDiapazon = .Range(iAddress) 'Evaluate(iAddress)
         Else
            MsgBox "Вы забыли выделить нужные ячейки ...", vbCritical, ""
         End If
    End With
    On Error Resume Next
    
    Dim iDiapazon As Range
    
    Set iDiapazon = Application.InputBox( _
        Prompt:="Выделите ячейку или диапазон", _
        Title:="", Type:=8)
    
    If Not iDiapazon Is Nothing Then
       iAddress = iDiapazon.Address
    Else
       MsgBox "Вы забыли выделить нужные ячейки ..."
    End If
    Совет : С помощью именованного аргумента Default можно задать текст по умолчанию, а с помощью аргументов Left, Top можно управлять размещением диалогового окна на экране, см. ниже пример ...
    Set iUserRange = Application.InputBox( _
        Prompt:="Выделите ячейку или диапазон", _
        Title:="", Default:=Selection.Address, _
        Left:=25, Top:=5, Type:=8)
    Обратите внимание на то, что переменная iUserRange представляет собой выбранный Вами об'ект Range, со всеми доступными свойствами и методами.

    Вариант II. (DialogSheet + EditBox)
    Выделите предварительно созданный элемент управления - текстовое поле (EditBox) Затем нажмите на правую кнопку мышки и в появившемся контекстном меню выберите пункт Формат объекта. Далее, нажмите на закладку Элемент управления и в разделе Тип вводимого значения установите переключатель напротив Ссылка и нажмите OK.

    Вариант III. (UserForm + RefEdit)
    Используйте элемент управления - RefEdit.
    Если данный элемент управления отсутствует, то в меню Вид выберите команду Панель инструментов. Затем в меню Сервис выберите команду Дополнительные элементы и в списке Доступные элементы управления выберите элемент RefEdit и нажмите OK.
  • Ответ :
  • Range("B3").Delete Shift:=xlToLeft 'удалить со сдвигом влево
    Range("A3").Delete Shift:=xlUp 'удалить со сдвигом вверх
    Примечание : Для программного удаления защищённых ячеек можно использовать этот [FAQ42]
  • Ответ : Актуально для MS Excel 97, 2000, XP

    Вариант I.
  • Range("A1:F10").Merge
    Вариант II.
    Range("A1:F10").MergeCells = True
    Если текст необходимо выравнить по центру, то нужно использовать :
    With Range("A1:F10")
         .VerticalAlignment = xlCenter
         .HorizontalAlignment = xlCenter
         .Merge '''''' Or
         .MergeCells = True
    End With
    Примечание :
  • Для того, чтобы при об'единении ячеек не получать предупреждение, которое появится если об'единяемый диапазон содержит несколько значений, нужно использовать [FAQ16]
  • Если рабочий лист защищён, то попытка об'единения ячеек приведёт к возникновению ошибки, которую можно избежать, если воспользоваться этим советом.
  • Ответ : Актуально для MS Excel 97, 2000, XP
  • Range("A1:F10").Merge Across:=True
    Примечание : [См. выше]
  • Ответ : Актуально для MS Excel 97, 2000, XP
  • Dim iColumn As Range
    For Each iColumn In Range("A1:F10").Columns
        iColumn.Merge 'iColumn.MergeCells = True
    Next
    Примечание :
  • [См. выше]
  • Если в дальнейшем понадобится об'единить ячейки по строкам, то в цикле уже не будет необходимости, см. предыдущий совет
  • Ответ : Актуально для MS Excel 97, 2000, XP

    Если в диапазоне, который требуется об'единить [см. выше], окажется несколько заполненных ячеек, то на экране появится предупреждение (в виде диалогового окна) информирующее пользователя о том, что подобное об'единение приведёт к потере всех значений, кроме левого верхнего. Именно это и произойдёт, если кликнуть кнопку OK, однако, иногда требуется не только об'единить сами ячейки, но и сохранить, при этом, данные всех заполненных ячеек. Что, собственно говоря, и позволяет сделать процедура MyMerge, вызов которой - должен заменить стандартное об'единение (см.ниже)
  • Private Sub Call_MyMerge()
        
       'Range("B11:D12").Merge
       'Range("B11:D12").MergeCells = True
        
        MyMerge Range("B11:D12") 'Worksheets(1).Range("B11:D12")
        
    End Sub
    
    Public Sub MyMerge(iDiapazon As Range)
        With Application
             Dim iCell As Range
             For Each iCell In iDiapazon
                 iText$ = iText$ & " " & CStr(iCell.Value)
             Next
             iText$ = .Trim(iText$)
             
             .DisplayAlerts = False
             With iDiapazon
                  .WrapText = True
                  .MergeCells = True '.Merge
                  .Value = iText$
             End With
             .DisplayAlerts = True
        End With
    End Sub

  • Ответ : Актуально для MS Excel 97, 2000, XP
  • If Range("A1").MergeCells = True Then
       iMergeArea = Range("A1").MergeArea.Address
       MsgBox "Адрес об'единённого диапазона : " & iMergeArea
    Else
       MsgBox "Указанная ячейка не является частью об'единённого диапазона"
    End If

    Воспроизведение любых опубликованных здесь материалов возможно только с письменного разрешения автора : Microsoft Excel 95, 97, 2000, XP

    © 2004-2016 Климов П.Ю. Все права защищены. WebDesign & Error's Klimoff