0

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

Ayibogan
  • 85
  • 1
  • 2
  • 7
  • 2
    `SpecialCell` -> `SpecialCells` – DisplayName Oct 21 '19 at 06:54
  • See the example code in the answer I provided on this page: https://stackoverflow.com/questions/45626312/excel-macro-to-capture-selected-filter-criteria/45626876#45626876 – Tony M Oct 21 '19 at 07:01

2 Answers2

1
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
DisplayName
  • 13,283
  • 2
  • 11
  • 19
  • Okay, so i tried this and this doesn't work as I intended, but I think i found the Problem i'm facing: .SpecialCells(xlCellTypeVisible) literally sorts out EVERY row, which has a column, which has no Value. Is there a way to make the "sorting out of rows" dependent on a single column? So e.g.: If column Z has a value, then include that row in the Range. – Ayibogan Oct 21 '19 at 08:00
  • @Ayibogan, see edited code. If it solved your question please consider marking the answer as accepted Thank you – DisplayName Oct 21 '19 at 11:17
  • thanks for the edited Code but i think you didn't understand my Problem: I want to the Range to be from Z7 to AK1048576 but ONLY get the rows, which have a VISIBLE value in Column Z. – Ayibogan Oct 21 '19 at 11:29
  • Look the Problem with xlCellTypeVisible is, that it literally sorts OUT every cell that is invisible/has no Value. However I want, that EVERY row, in which the column Z has a value, is included in the Range, even if the value is 0/empty. Isn't that possible? Should I try to save those cells in a 2 dimensional Array instead? – Ayibogan Oct 21 '19 at 11:49
0

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.

Judge
  • 181
  • 5