CODEWITHSUNDEEP
excelvba
I Paul Ema
I Paul Ema

Reputation: 33

How to offset visible cells only

I am writing a VBA code in which I must get the row down by one, from the table header, for an exact column. For this I use the Offset(1) property of the ranges. The problem is I am working with filtered data and the offset gets me to G2 for example, instead of going to Gx, where x is the first visible row after the table header. How do i better accomplish this? Thank you

Upvotes: 2

Views: 15200

Answers (4)

Asif Ahmed
Asif Ahmed

Reputation: 11

I have got a mathematical VBA solution to get to the first visible cell in filtered data.

    Range("A10000").End(xlUp).Select
    
    last_row = ActiveCell.Row
    
    Range(Selection, Selection.End(xlUp)).Select
    
    VisCellCount = WorksheetFunction.Subtotal(3, Selection)
    
    first_row = last_row - VisCellCount + 2
    
    Range("A" & first_row).Select

Upvotes: 1

Junaid
Junaid

Reputation: 111

I solved the very same problem by modifing @TheJaswi code, as it was not working for me and going to very first column header offsetting to visible rows:

'Getting Total columns count in the given table
col = Worksheets(1).ListObjects(1).ListColumns.Count
ActiveSheet.AutoFilter.Range.Offset(1, col - 1).SpecialCells(xlCellTypeVisible)(1).Select

Upvotes: 0

Thejaswi
Thejaswi

Reputation: 1

ActiveSheet.AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row

Upvotes: 0

Pawel Czyz
Pawel Czyz

Reputation: 1645

You can try this:

Sub selectFirstVisibleCell()

    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "G").End(xlUp).Row

    Range("G1:G" & lastRow).Offset(1, 0).SpecialCells(xlCellTypeVisible).Areas(1).Rows(1).Select

End Sub

Upvotes: 6

Related Questions