Page tree

Welcome to FreeSoftwareServers Confluence Wiki

Skip to end of metadata
Go to start of metadata

Find Second Visible Rows "Absolute" Row Number:

Sub SecondVisiRowTest()
 Dim SecondVisiRow As Integer
 SecondVisiRow = AbsoluteVisibleRow(ActiveSheet, 2)
 Debug.Print SecondVisiRow
End Sub
Public Function AbsoluteVisibleRow(ws As Worksheet, visirowtarget As Integer) As Integer

Dim visRng As Range
Set visRng = ws.UsedRange.SpecialCells(xlCellTypeVisible)

Dim r As Range, i As Integer
i = 0
For Each r In visRng.Rows
 i = i + 1
 If i = visirowtarget Then
  AbsoluteVisibleRow = r.Row
 ElseIf i > visirowtarget Then
  Exit Function
 End If
End Function
With FilterWS
  .AutoFilterMode = False
 With .UsedRange
  .AutoFilter field:=3, Criteria1:=FilterStr ' 3 = Column C
  'Debug.Print FilterStr
 End With
 Set rang = FilterWS.UsedRange.Offset(1, 0)
 Set rang = rang.Resize(rang.Rows.Count - 1)
 On Error Resume Next
 Set rang = rang.SpecialCells(xlCellTypeVisible)
 If Err.Number = 0 Then
  Call FindLast(ResultsWS)
  lRow = lRow + 1
  ResultsWS.Range("A" & lRow).PasteSpecial
 End If
  On Error GoTo 0
  .AutoFilterMode = False
 End With

Note, you can also delete all BUT Match via :

Criteria1:="<>B***-***" ' <>

Note: Check this for Avoiding Header Row

  • No labels