Delete all rows except first and last row

Solved/Closed
urgentMe - Nov 13, 2008 at 10:56 PM
 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

2 responses

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

3