VB, How to find last row & delete inbetween
Closed
weenie
-
Jul 14, 2011 at 07:57 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jul 23, 2011 at 01:24 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jul 23, 2011 at 01:24 AM
Related:
- VB, How to find last row & delete inbetween
- How to delete whatsapp account without login - Guide
- How to delete a row in a table in word - Guide
- Saints row 2 cheats - Guide
- How to delete whatsapp account without phone - Guide
- How to delete snapchat account - Guide
1 response
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jul 23, 2011 at 01:24 AM
Jul 23, 2011 at 01:24 AM
try this
Public Sub doDelRows() Dim lLastRow As Long Dim sSheet As String sSheet = "Sheet1" lLastRow = getItemLocation("*", Sheets(sSheet).Cells) If (lLastRow > 3) _ Then Rows("3:" & lLastRow -1).Delete End If End Sub Public Function getItemLocation(sLookFor As String, _ rngSearch As Range, _ Optional bFullString As Boolean = True, _ Optional bLastOccurance As Boolean = True, _ Optional bFindRow As Boolean = True) As Long 'find the first/last row/column within a range for a specific string Dim Cell As Range Dim iLookAt As Integer Dim iSearchDir As Integer Dim iSearchOdr As Integer If (bFullString) _ Then iLookAt = xlWhole Else iLookAt = xlPart End If If (bLastOccurance) _ Then iSearchDir = xlPrevious Else iSearchDir = xlNext End If If Not (bFindRow) _ Then iSearchOdr = xlByColumns Else iSearchOdr = xlByRows End If With rngSearch If (bLastOccurance) _ Then Set Cell = .Find(sLookFor, .Cells(1, 1), xlValues, iLookAt, iSearchOdr, iSearchDir) Else Set Cell = .Find(sLookFor, .Cells(.Rows.Count, .Columns.Count), xlValues, iLookAt, iSearchOdr, iSearchDir) End If End With If Cell Is Nothing Then getItemLocation = 0 ElseIf Not (bFindRow) _ Then getItemLocation = Cell.Column Else getItemLocation = Cell.Row End If Set Cell = Nothing End Function