I have various items in Col F
I have tried to write code to retain "Fixed asset ledger" in Col f from row 2 onwards and all the other rows are to be deleted. The code deletes everything from row 2 onward
I have attached my workbook . Please test & amend code
Sub Retain_Fixed_asset_Ledger()
Dim rngTable As Range
Dim lngIndex As Long
Dim rngCheck As Range
Dim varOffset As Variant
Const strKey As String = "Fixed*"
With ActiveSheet
Set rngTable = Range(.Range("F2"), .Range("F" & .Rows.Count).End(xlUp))
End With
For lngIndex = rngTable.Row + 2 To rngTable.Rows.Count + rngTable.Row Step 3
Set rngCheck = rngTable(lngIndex - rngTable.Row - 1).Resize(3)
varOffset = Application.Match(strKey, rngCheck, 0)
Next lngIndex
With rngTable
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
End Sub
Your assistance is most appreciated
Bookmarks