Excel 2007 - Deleting the last three rows
Closed
dumow880
Posts
7
Registration date
Thursday July 7, 2011
Status
Member
Last seen
July 17, 2012
-
Jul 18, 2011 at 05:47 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jul 19, 2011 at 11:35 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jul 19, 2011 at 11:35 AM
Related:
- Excel 2007 - Deleting the last three rows
- Save as pdf office 2007 - Download - Other
- Excel marksheet - Guide
- Excel free download - Download - Spreadsheets
- Number to words in excel - Guide
- Kernel for excel - Download - Backup and recovery
2 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jul 18, 2011 at 07:05 PM
Jul 18, 2011 at 07:05 PM
if a row has a data ? a data can be in any cell from A to the last possible one.
if you did mean "row" then you can do a find for "*" and find the last row that had any thing. Repeat same 2 more times and you have your three rows gone
if you did mean "row" then you can do a find for "*" and find the last row that had any thing. Repeat same 2 more times and you have your three rows gone
dumow880
Posts
7
Registration date
Thursday July 7, 2011
Status
Member
Last seen
July 17, 2012
1
Jul 19, 2011 at 09:11 AM
Jul 19, 2011 at 09:11 AM
Thanks for replying.
Could you, or any other forum reader, assist me in a formula to put into a macro to do the "*" or a relevant formula to scan to the bottom and erase the entire last three rows with data. There are no gaps in rows from top to bottom. From row 1 all the way to the last row there will be some kind of data on the row. It also needs to work on any sheet. The overall big picture is that I receive a standard template with data that varies. But i need everything but the last three rows gone because they are just a standard fixed text.
Could you, or any other forum reader, assist me in a formula to put into a macro to do the "*" or a relevant formula to scan to the bottom and erase the entire last three rows with data. There are no gaps in rows from top to bottom. From row 1 all the way to the last row there will be some kind of data on the row. It also needs to work on any sheet. The overall big picture is that I receive a standard template with data that varies. But i need everything but the last three rows gone because they are just a standard fixed text.
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jul 19, 2011 at 11:35 AM
Jul 19, 2011 at 11:35 AM
Try this
Public Sub doDeleteRow()
Dim sSheet As String
Dim lRow As Long
Dim iDelCount As Integer
sSheet = "Sheet1"
lRow = getItemLocation("*", Sheets(sSheet).Cells)
Select Case lRow
Case lRow > 2
Rows(lRow - 2 & ":" & lRow).Delete
Case lRow = 2
Rows(lRow - 1 & ":" & lRow).Delete
Case lRow = 1
Rows(lRow).Delete
Case Else
End Select
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
