Delete all rows except first and last row [Solved/Closed]

urgentMe - Nov 13, 2008 at 10:56 PM - Latest reply:  fairbud
- Jan 18, 2010 at 06:14 AM
Hello,

I have an excel file, I need to delete all rows in this file except for the first and second row.
First Row = header row
last row = contains total value of the items in header row

thanks
See more 

2 replies

Best answer
2
Thank you
I just have this code that will return no. of row of the first and last column. now, I want to delete other rows. Maybe someone could help me manipulate/ edit this code:

Sub Test_xlFirstLastRows()
     
     '       Target Application:  MS Excel
     '       Demonstration:  display first and last non-blank rows in the active sheet
     '                       and one target sheet
     
    Dim SheetName As String
     '
     '           display sheet name and results from xlFirstRow and xlLastRow
     '           for the active sheet.  Since activesheet is assumed if procs are called
     '           without a passed arguement, use that method here
     '
    MsgBox "Worksheet name = " & ActiveSheet.Name & vbCrLf & _
    "First non-blank row = " & xlFirstRow & vbCrLf & _
    "Last non-blank row = " & xlLastRow, vbInformation, _
    "Active Sheet Demonstration"
     '
     '           display sheet name and results from xlFirstRow and xlLastRow
     '           for "Sheet4".  Since this is not the active sheet, the sheet must
     '           be defined via the passed arguement.
     '
    SheetName = "Sheet4"
    MsgBox "Worksheet name = " & SheetName & vbCrLf & _
    "First non-blank row = " & xlFirstRow(SheetName) & vbCrLf & _
    "Last non-blank row = " & xlLastRow(SheetName), vbInformation, _
    "Passed Sheet Name Demonstration"
     
     
End Sub

Function xlFirstRow(Optional WorksheetName As String) As Long
     
     '    find the first populated row in a worksheet
     
    If WorksheetName = vbNullString Then WorksheetName = ActiveSheet.Name
    With Worksheets(WorksheetName)
        On Error Resume Next
        xlFirstRow = .Cells.Find("*", .Cells(.Cells.Count), xlFormulas, _
        xlWhole, xlByRows, xlNext).Row
        If Err <> 0 Then xlFirstRow = 0
    End With
     
End Function
 
Function xlLastRow(Optional WorksheetName As String) As Long
     
     '    find the last populated row in a worksheet
     
    If WorksheetName = vbNullString Then WorksheetName = ActiveSheet.Name
    With Worksheets(WorksheetName)
        On Error Resume Next
        xlLastRow = .Cells.Find("*", .Cells(1), xlFormulas, _
        xlWhole, xlByRows, xlPrevious).Row
        If Err <> 0 Then xlLastRow = 0
    End With
     
End Function

Thank you, urgentMe 2

Something to say? Add comment

CCM has helped 1676 users this month

0
Thank you
Try:-

Sub DeleteRow()
Dim i1 As Long
Dim iMax As Long
iMax = Cells.SpecialCells(xlCellTypeLastCell).Row

For i1 = iMax - 1 To 2 Step -1
Rows(i1).EntireRow.Delete
Next i1


End Sub