Page tree

Welcome to FreeSoftwareServers Confluence Wiki

Skip to end of metadata
Go to start of metadata

https://stackoverflow.com/questions/58636753/find-and-delete-row-with-multiple-criteria/58637084#58637084

https://stackoverflow.com/questions/16901436/efficient-way-to-delete-entire-row-if-cell-doesnt-contain

https://trumpexcel.com/vba-autofilter/

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
Next
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
  rang.Copy
  Call FindLast(ResultsWS)
  lRow = lRow + 1
  ResultsWS.Range("A" & lRow).PasteSpecial
 End If
  On Error GoTo 0
  .AutoFilterMode = False
 End With
 FilterWS.Rows(1).Copy
 ResultsWS.Rows(1).PasteSpecial
 ResultsWS.Activate

Note, you can also delete all BUT Match via :

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

Note: Check this for Avoiding Header Row

https://stackoverflow.com/questions/38410056/excel-vba-select-all-filtered-rows-except-header-after-autofilter


  • No labels