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
- Vb net find last row in excel sheet - Guide
- Saints row 2 cheats - Guide
- How to delete a row in word - Guide
- How to delete skype account permanently - Guide
- How to delete whatsapp account without login - 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