Excel vba диапазон только видимых ячеек для массива

Я пытаюсь получить все значения из видимых ячеек из диапазона в массив. Мой код только заставляет массив нести значения до первой невидимой ячейки, а затем останавливается.

Public Function ListeMaschinen() As Variant

Dim Auswahl As Range

With Sheets("qry_TechnischesDatenblatt")
Set Auswahl = .Range(.Range("A2:B2"), .Range("A2:B2").End(xlDown)).SpecialCells(xlCellTypeVisible)
End With

ListeMaschinen = Auswahl

End Function

Если я выберу диапазон, он покажет все ячейки, отмеченные как я хочу получить.

Auswahl.Select

Я не смог понять, почему, вы можете мне помочь?

Большое спасибо! подветренный

Источник

4 ответа

Решение

Здесь я добавил ячейки диапазона в массив.

Sub examp()
Dim rng As Range, cll As Range, i As Integer, a(100) As Variant
Set rng = Range(Range("A2:B2"), Range("A2:B2").End(xlDown)).SpecialCells(xlCellTypeVisible)
i = 0
For Each cll In rng
a(i) = cll.Value
i = i + 1
Next
End Sub
Источник

В дополнение к моим комментариям ранее, вот метод, который будет работать с некоторыми ограничениями:

Вы не можете иметь более 65536 строк данных; и вы не можете иметь действительно длинный текст (911 символов +) или пустые видимые ячейки; и данные не должны содержать строку "|~|"

Если эти условия соблюдены, вы можете использовать что-то вроде этого:

Dim v
Dim sFormula              As String
With Selection
    sFormula = "IF(SUBTOTAL(103,OFFSET(" & .Cells(1).Address(0, 0) & ",row(" & .Address(0, 0) & ")-min(row(" & .Address(0, 0) & ")),1))," & .Address(0, 0) & ",""|~|"")"
End With
Debug.Print sFormula
v = Filter(Application.Transpose(Evaluate(sFormula)), "|~|", False)

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

Источник

Я думаю, что ваша проблема связана с

.SpecialCells(xlCellTypeVisible)

Когда я делаю это:

Public Function ListeMaschinen() As Variant
  Dim  Auswahl As Range
  With Sheets("qry_TechnischesDatenblatt")
      Set Auswahl = .Range(.Range("A2:B2"),  .Range("A2:B2").End(xlDown)).SpecialCells(xlCellTypeVisible)
  End With
  MsgBox Auswahl.Address
  Set ListeMaschinen = Auswahl
  'Auswahl.Select
End Function

Я получаю Address состоит из 2 частей: видимых частей! введите описание изображения здесь

Но когда я удаляю SpecialCells

Public Function ListeMaschinen() As Variant
  Dim Auswahl As Range
  With Sheets("qry_TechnischesDatenblatt")
      Set Auswahl = .Range(.Range("A2:B2"), .Range("A2:B2").End(xlDown))
  End With
  MsgBox Auswahl.Address
  Set ListeMaschinen = Auswahl
End Function

Я получаю одну часть, которую я получаю при использовании Select тоже.

введите описание изображения здесь

Я проверял!

Sub test()
  Dim myVar As Variant
  Dim i As Integer
  i = 0
  Set myVar = ListeMaschinen()
  For Each C In myVar
    i = i + 1
    MsgBox C.Value & C.Address & "-" & i
  Next
End Sub
Источник

Здравствуйте:) Я пытался найти способ перебрать видимые строки в таблице, не просматривая все строки и не проверяя, видны ли они, поскольку это занимало слишком много времени на большой таблице. Ниже представлено решение, которое я смог придумать. Это функция, которая возвращает массив абсолютных номеров строк видимых строк в заданном диапазоне.

Function GetVisibleRows(LookupRange As Range) As Integer()
    Dim VisibleRange As Range, Index As Integer, Area As Range
    Static VisibleRows() As Integer    
    
    Set VisibleRange = LookupRange.SpecialCells(xlCellTypeVisible)
    ReDim VisibleRows(0)
    Index = 0
    
    For Each Area In VisibleRange.Areas
        If Index = 0 Then
            VisibleRows(Index) = Area.Row
            ReDim Preserve VisibleRows(Index + 1)
        End If
        
        Index = UBound(VisibleRows())
        
        If VisibleRows(Index - 1) <> Area.Row Then
            VisibleRows(Index) = Area.Row
            ReDim Preserve VisibleRows(Index + 1)
        End If
    Next
    
    ' Remove last empty item
    ReDim Preserve VisibleRows(UBound(VisibleRows()) - 1)
    GetVisibleRows = VisibleRows
End Function

Если вы хотите использовать эту функцию в сценарии поиска, вам необходимо преобразовать абсолютные номера строк, возвращаемых функцией, в относительные номера строк в таблице. Следующее сработало для меня.

RowIndex = ReturnedRowIndex - LookupRange.Rows(1).Row + 1

Удачи!

Источник

В своем коде вы устанавливаете переменную Variant равной объекту Range без использования оператора Set.

Следующее работает с небольшим тестированием, которое я сделал. Конечно, если вы объявляете тип функции и другие переменные как тип Range, это также работает.

Option Explicit
Sub test()
Dim myVar As Variant

Set myVar = myList()

Debug.Print myVar.Address

End Sub

Public Function myList() As Variant
Dim myRng As Range

With Sheets("Sheet1")
    Set myRng = .Range(.Range("A1:B1"), .Range("A1:B1").End(xlDown)).SpecialCells(xlCellTypeVisible)
End With

Debug.Print myRng.Address

Set myList = myRng

End Function
Источник
Другие вопросы по тегам