CODEWITHSUNDEEP
excelvba
LouLav
LouLav

Reputation: 149

How to get all visible rows in one range

I'm trying to get all my filtered data in one range variable but it doesn't work.

When the visible datas are continuous (rows 25 to 200), i've no problem but when the visible datas are discontinuous (rows 25 to 27, then 43 to 47, then 60 to 92) it only get the first range (rows 25 to 27)

Here is my code :

datas = dataSheet.Range("A2:L" & dataSheet. 
[A65000].End(xlUp).Row).SpecialCells(xlCellTypeVisible).Value

Do you have any tip ?

Thank you for your answer.

Louis

Upvotes: 0

Views: 680

Answers (2)

tigeravatar
tigeravatar

Reputation: 26650

It sounds like you're trying to populate an array variable named datas, which is successful if your range is continuous, but only gets the first section when discontinuous. And what you're looking for is to populate the array with all of the data from the discontinuous range.

That is possible, and there are two approaches. The first is to copy the discontinuous range and paste it into a temp worksheet. The pasted range will be continuous and then you can load it into the array normally as shown in your original code. The second is to populate the array directly, but you'll have to loop through each visible cell to do this.

Method 1 (use temp worksheet):

Sub tgrTempWS()

    Dim dataSheet As Worksheet
    Dim tempSheet As Worksheet
    Dim rData As Range
    Dim datas As Variant

    Set dataSheet = ActiveWorkbook.Sheets("Sheet1")
    On Error Resume Next
    Set rData = dataSheet.Range("A2:L" & dataSheet.[A65000].End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    If rData Is Nothing Then Exit Sub   'No data

    Set tempSheet = dataSheet.Parent.Sheets.Add
    rData.Copy tempSheet.Range("A1")
    datas = tempSheet.Range("A1").CurrentRegion.Value
    Application.DisplayAlerts = False
    tempSheet.Delete
    Application.DisplayAlerts = True

    'do stuff with your datas array variable here

End Sub

Method 2 (loop through visible cells):

Sub tgrLoop()

    Dim dataSheet As Worksheet
    Dim rData As Range
    Dim rCell As Range
    Dim datas As Variant
    Dim lRow As Long, lCol As Long
    Dim i As Long, j As Long

    Set dataSheet = ActiveWorkbook.Sheets("Sheet1")
    On Error Resume Next
    Set rData = dataSheet.Range("A2:L" & dataSheet.[A65000].End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    If rData Is Nothing Then Exit Sub   'No data

    ReDim datas(1 To Intersect(rData, rData.Areas(1).Resize(, 1).EntireColumn).Cells.Count, 1 To rData.Columns.Count)
    For Each rCell In rData.Cells
        If lRow = 0 Then
            lRow = rCell.Row
            i = 1
        ElseIf rCell.Row > lRow Then
            i = i + 1
            lRow = rCell.Row
        End If
        If lCol = 0 Or rCell.Column < lCol Then
            lCol = rCell.Column
            j = 1
        ElseIf rCell.Column > lCol Then
            j = j + 1
            lCol = rCell.Column
        End If
        datas(i, j) = rCell.Value
    Next rCell

    'do stuff with your datas array variable here

End Sub

Upvotes: 1

J&#233;r&#233;my Gamba
J&#233;r&#233;my Gamba

Reputation: 80

From MSDN about Range Object : "Represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3-D range."

That's why you only get the first range. Have a look at this page to refer to multiple ranges.

Upvotes: 0

Related Questions