Set Data range excluding Hidden rows

[padadof2]

dim data as variant
Data = shB.Range(Cells(9, 1), Cells(LR, 12)).SpecialCells(xlCellTypeVisible)

If LR =20, but a hidden row exists at row 12, then the data variant only grabs rows 9-11, not all the way to row 20. What am I doing wrong?

Thank you in advance

 

[HaHoBe]

Hi padadof2,

maybe

Public Sub MrE_1224320_1615809()
' https://www.mrexcel.com/board/threads/set-data-range-excluding-hidden-rows.1224320/
' Created: 20221210
' By:      HaHoBe
Dim rngWork         As Range
Dim lngLastRow      As Long
Dim wsWork          As Worksheet

Set wsWork = ActiveSheet      'change to suit
lngLastRow = wsWork.Range("A" & wsWork.Rows.Count).End(xlUp).Row    'correct column if needed
With wsWork.Range(Cells(9, 1), Cells(lngLastRow, 12))
  If WorksheetFunction.CountA(.Columns(1)) > 0 Then
    Set rngWork = .SpecialCells(xlCellTypeVisible)
  End If
End With
If Not rngWork Is Nothing Then Debug.Print rngWork.Address
'more code to come

Set rngWork = Nothing
Set wsWork = Nothing

End Sub

Ciao,
Holger

 

[Peter_SSs]

@padadof2
It seems to me that your thread title is not describing what your code is trying to do. From your couple of code lines it appears that you are trying to get the values from the visible cells in that range into a variant array. If that is so, then you could try this.

Dim Data As Variant
Dim rA As Range
Dim sR As String

For Each rA In shB.Range(shB.Cells(9, 1), shB.Cells(LR, 1)).SpecialCells(xlCellTypeVisible).Areas
  sR = sR & " " & Join(Application.Transpose(Evaluate("row(" & rA.Row & ":" & rA.Row + rA.Rows.Count - 1 & ")")))
Next rA

Data = Application.Index(shB.Cells, Application.Transpose(Split(Mid(sR, 2))), Application.Transpose(Evaluate("row(1:12)")))

 

[kvsrinivasamurthy]

Try this

data = ShB.Range(Cells(5, 1), Cells(LR, 2)).SpecialCells(xlCellTypeVisible).Value

If it does not work use

ShB.Range(Cells(5, 1), Cells(LR, 2)).SpecialCells(xlCellTypeVisible).Select
data = ShB.Range(Cells(5, 1), Cells(LR, 2)).SpecialCells(xlCellTypeVisible).Value

 

[Peter_SSs]

Try this

.. I'm wondering if you tried it with the setup described?

Both codes are identical in practice since the 'Select' in the second one has no impact on the following line and in both cases the line that actually populates the array is the same** as the line from post #1 given that ".Value" is the default property of the range. The problem with that is only the first area in the visible range is fed into the Data array.

(** apart from starting at a different row and using columns A:B as opposed to A:L from post 1)

 

[kvsrinivasamurthy]

If you want store as single Array.

Sub Macro1()
Dim data As Variant
Dim Str As String
Dim C As Range
LR = 15
For Each C In Range(Cells(5, 1), Cells(LR, 2)).SpecialCells(xlCellTypeVisible)
If C <> "" And C.EntireRow.Hidden = False Then Str = Str & "," & C
Next C
data = Split(Mid(Str, 2), ",")
End Sub

 

[kvsrinivasamurthy]

Store as 2D array

Sub Macro2()
Dim data As Variant
Dim Str As String
Dim C As Range
Dim LR As Long
LR = 15
ReDim ary(1 To LR - 5 + 1, 1 To 12)
Ro = 0: Pro = 0: Clm = 1
For Each C In Range(Cells(5, 1), Cells(LR, 12)).SpecialCells(xlCellTypeVisible)
If C.EntireRow.Hidden = False Then
    If Pro <> C.Row Then
    Ro = Ro + 1: Clm = 1
    End If
ary(Ro, Clm) = C
Clm = Clm + 1: Pro = C.Row
End If
Next C
End Sub

 

[padadof2]

@padadof2
It seems to me that your thread title is not describing what your code is trying to do. From your couple of code lines it appears that you are trying to get the values from the visible cells in that range into a variant array. If that is so, then you could try this.

Dim Data As Variant
Dim rA As Range
Dim sR As String

For Each rA In shB.Range(shB.Cells(9, 1), shB.Cells(LR, 1)).SpecialCells(xlCellTypeVisible).Areas
  sR = sR & " " & Join(Application.Transpose(Evaluate("row(" & rA.Row & ":" & rA.Row + rA.Rows.Count - 1 & ")")))
Next rA

Data = Application.Index(shB.Cells, Application.Transpose(Split(Mid(sR, 2))), Application.Transpose(Evaluate("row(1:12)")))

Thank you! This is exactly what I needed. Now I need to digest why this works.

 

[Peter_SSs]

Thank you! This is exactly what I needed.

You're welcome. Thanks for the follow-up.

Now I need to digest why this works.

While you are doing that you should note that for the particular example you gave originally (Range A9:L20 with row 12 hidden) this code requires just two loops then reads all the values into the array at once. Also, the final array will be just the right size to exactly hold the relevant rows/columns of data.