Macro to delete first and last row
 

I need to delete the first and last row of a file everytime before
continuing on with the macro. Can anyone provide any tips? The first
line will always contain the same text, but the last line will vary
since the file is an export and the exported date/time is always
appended (i.e. 'Exported on 12/21/2006 3:05pm).

I found this code on the net to delete a line, but I modified it to try
and get it to use multiple conditions to delete:


Sub OpDivTickets()

Workbooks.Open Filename:= _
"C:\Documents and Settings\me\My Documents\Projects &
Proposals\ABC\Ticket Prioritization Meetings\Request Search
Results.xls"

Dim rng As Range
Dim what As String
what = "Request Search Results"
Do
Set rng = ActiveSheet.UsedRange.Find(what)
If rng Is Nothing Then
Exit Do
Else
Rows(rng.Row).Delete
End If


Dim rng As Range
Dim what As String
what like "Exported on"
Do
Set rng = ActiveSheet.UsedRange.Find(what)
If rng Is Nothing Then
Exit Do
Else
Rows(rng.Row).Delete
End If
Loop

Any help is appreciated

Macro to delete first and last row
 

With the info provided.
Sub deletefirstandlastrows()
lr = Cells(Rows.Count, "a").End(xlUp).Row
Range("a1,a" & lr).EntireRow.Delete
End Sub

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
I need to delete the first and last row of a file everytime before
continuing on with the macro. Can anyone provide any tips? The first
line will always contain the same text, but the last line will vary
since the file is an export and the exported date/time is always
appended (i.e. 'Exported on 12/21/2006 3:05pm).

I found this code on the net to delete a line, but I modified it to try
and get it to use multiple conditions to delete:


Sub OpDivTickets()

Workbooks.Open Filename:= _
"C:\Documents and Settings\me\My Documents\Projects &
Proposals\ABC\Ticket Prioritization Meetings\Request Search
Results.xls"

Dim rng As Range
Dim what As String
what = "Request Search Results"
Do
Set rng = ActiveSheet.UsedRange.Find(what)
If rng Is Nothing Then
Exit Do
Else
Rows(rng.Row).Delete
End If


Dim rng As Range
Dim what As String
what like "Exported on"
Do
Set rng = ActiveSheet.UsedRange.Find(what)
If rng Is Nothing Then
Exit Do
Else
Rows(rng.Row).Delete
End If
Loop

Any help is appreciated

Macro to delete first and last row
 

Just cleaning up the code you have...

Sub OpDivTickets()
Dim rng As Range
Dim what As String

Workbooks.Open Filename:= _
"C:\Documents and Settings\me\My Documents\Projects" & _
"Proposals\ABC\Ticket Prioritization Meetings\Request Search Results.xls"
' Sheets("You Name It").Activate

what = "Request Search Results"
Set rng = ActiveSheet.UsedRange.Find(what)
If rng Is Nothing Then
Else
rng.EntireRow.Delete
End If

what = "Exported on"
Set rng = ActiveSheet.UsedRange.Find(what)
If rng Is Nothing Then
Else
rng.EntireRow.Delete
End If
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


wrote in message oups.com...
I need to delete the first and last row of a file everytime before
continuing on with the macro. Can anyone provide any tips? The first
line will always contain the same text, but the last line will vary
since the file is an export and the exported date/time is always
appended (i.e. 'Exported on 12/21/2006 3:05pm).

I found this code on the net to delete a line, but I modified it to try
and get it to use multiple conditions to delete:


Sub OpDivTickets()

Workbooks.Open Filename:= _
"C:\Documents and Settings\me\My Documents\Projects &
Proposals\ABC\Ticket Prioritization Meetings\Request Search
Results.xls"

Dim rng As Range
Dim what As String
what = "Request Search Results"
Do
Set rng = ActiveSheet.UsedRange.Find(what)
If rng Is Nothing Then
Exit Do
Else
Rows(rng.Row).Delete
End If


Dim rng As Range
Dim what As String
what like "Exported on"
Do
Set rng = ActiveSheet.UsedRange.Find(what)
If rng Is Nothing Then
Exit Do
Else
Rows(rng.Row).Delete
End If
Loop

Any help is appreciated