CODEWITHSUNDEEP
excelvba
Ayibogan
Ayibogan

Reputation: 95

How do I make a Range of a certain area, that has been filtered out?

So first of all, I wanna write a VBA Code, which allows me to pick a certain range of an Excel sheet, to then copy SOME of the needed values to another worksheet. The issue with this is, that in the Excel sheet of which i take Information from, has some filters applied.

So i found the solution with the method (?) .SpecialCells(xlCellTypeVisible) but the Problem again is, that it works for 1 column, but not for Ranges with more than one column. For Ranges with more than one column, it only picks the first row

Dim rng As Range
Set rng = src.Worksheets("l04").Range(src.Worksheets("l04").Range("Z7:AK7"), src.Worksheets("l04").Range("Z7:AK7").End(xlDown)).SpecialCells(xlCellTypeVisible)

My expected result from this Line of Code should be, that the Range rng is set from Z7 to AK7 all the way down to the maximum number of rows, but only those which are visible.

Edit1: Changed SpecialCell --> SpecialCells

Upvotes: 0

Views: 365

Answers (2)

DisplayName
DisplayName

Reputation: 13386

Dim cell As Range
Dim lastRow As Long
With src.Worksheets("104")
    lastRow = .Cells(.Rows.Count, "Z").End(xlUp).row
    With .Range("Z7:AK" & lastRow)
        For Each cell In .Columns(1).SpecialCells(xlCellTypeVisible)
            Debug.Print Intersect(.Cells, cell.EntireRow).Address ' change this to what you actually need to grab from each visible cell
        Next
    End With
End With

Upvotes: 1

Judge
Judge

Reputation: 307

Based on some clues in your question, you may find that using the Intersect Method is advantageous.

Dim rng as Range

With src.Worksheets("l04")

    'gets all visible cells within the used range on your sheet    
    set rng = .UsedRange.SpecialCells(xlCellTypeVisible) 

    'Use the Intersect method to select specific columns
    set rng = Intersect(rng, .range("AB:AB, AD:AD"))

End With

Note: This will not select down to last row (i.e. row 1,048,576), only to the last row with data in the specified range.

Upvotes: 0

Related Questions