0

How can I get all row number of visible cell after filtering? I need to have number of each visible row to copy some cells. For example I have 15 rows. After filtering there are only Row 2nd and Row 10th visible. How find Cell (10,2) and (2,2)? For example there are 15 rows in sheet1. After filtering only row 2 and row 10 are visible. I want to copy cell (10,2) and cell(2,2). But I could not.

sheet2.Cells(2, w) = sheet1.Cells(k, 1)

It put k=1 and copy from the first row. It should k=2 and k=10.

cybernetic.nomad
  • 6,100
  • 3
  • 18
  • 31
Kevin Shah
  • 1
  • 1
    Possible duplicate of [Copy/Paste/Calculate Visible Cells from One Column of a Filtered Table](https://stackoverflow.com/questions/17531128/copy-paste-calculate-visible-cells-from-one-column-of-a-filtered-table) – BigBen May 30 '19 at 16:23
  • 4
    Use [`Range.SpecialCells`](https://learn.microsoft.com/en-us/office/vba/api/excel.range.specialcells) and `xlCellTypeVisible`. You don't need to find the row number at all. – BigBen May 30 '19 at 16:25
  • How? It shows only visible value. I need exact value. – Kevin Shah May 31 '19 at 18:59
  • Did you try the approach in the linked question above? If so, what errors are you having? "Visible cells" refers to whether the cell is hidden/filtered, not to how it is displayed. The underlying `.Value` of the `Range` is what you want. – BigBen May 31 '19 at 19:05
  • I need to find the row number of some special cell in filtered area. After that I use the values. For example after filtering the first row is row number 98. I need to copy cell (98,2). – Kevin Shah Jun 03 '19 at 16:01
  • Yep. You can use `Range.SpecialCells` and `xlCellTypeVisible` to identify which cells are still visible after the filter has been applied. Then you can copy those cells as needed. Normally with this, you don't need to find the row number. Maybe share more of your code, but from the problem as you've described it, `Range.SpecialCells` should work. – BigBen Jun 03 '19 at 16:26

0 Answers0