Row number of filtered row

[DZ]

I need to get the row number of the second visible row in the current region.
The current region is filtered.

Example
If the current region starts at row 6, the second visible row is 28 because
the current region is filtered.

The following returns 7, because it not looking at visible rows only. I want
the return value to be 28, the second VISIBLE row.

Dim intRowNumber As Integer
intRowNumber = ActiveCell.CurrentRegion.Rows(2).Row

Thanks

 

[Mike H]

Hi,

Areas is what you want
Area 1 = heading
Area 2 = First visible row etc

so this returns the row number of the second visible row

Sub Macro2()
If ActiveSheet.FilterMode = True Then
x = ActiveSheet.UsedRange.SpecialCells _
(xlCellTypeVisible).Areas(3).Row
End If
End Sub

Mike

 

[DZ]

Wonderful

Regards
Angelo DiBraccio

DZ

 

[Dave Peterson]

Are you trying to find the first visible detail row (not including the headers)?

Or are you trying to find the row number of the second visible detail row?

Option Explicit
Sub testme()

Dim WhatRow As Long
Dim VRng As Range

With ActiveSheet.AutoFilter.Range
Set VRng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
End With

'first visible row excluding the headers
WhatRow = VRng.Cells(1).Row
MsgBox WhatRow

If VRng.Areas(1).Cells.Count > 1 Then
WhatRow = VRng.Cells(1).Row + 1
Else
WhatRow = VRng.Areas(2).Cells(1).Row
End If

MsgBox WhatRow

End Sub

 

[Dave Peterson]

And in case there aren't multiple areas -- or any visible details:

Option Explicit
Sub testme()

Dim WhatRow As Long
Dim VRng As Range

Set VRng = Nothing
On Error Resume Next
With ActiveSheet.AutoFilter.Range
Set VRng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
End With
On Error GoTo 0

If VRng Is Nothing Then
MsgBox "no visible details"
Else
'first visible row excluding the headers
WhatRow = VRng.Cells(1).Row
MsgBox WhatRow

If VRng.Areas(1).Cells.Count > 1 Then
WhatRow = VRng.Cells(1).Row + 1
Else
If VRng.Areas.Count > 1 Then
WhatRow = VRng.Areas(2).Cells(1).Row
Else
WhatRow = -9999
End If
End If

MsgBox WhatRow
End If

End Sub