Help optimizing this 2003 vba code to run quicker in 2010

[kcsmiles]

I have a macro that went from running in 1-2 minutes in 2003 to taking 35-40 minutes in 2010. I have been trying to figure out what is causing the slow down. I think I have narrorwed it to this section of code.

Public Property Get StartRow(sheetName, TextValue, Optional theOccurrence As Integer)
Dim Count As Integer
Set c = Sheets(sheetName).Cells.Find(What:=TextValue, After:=Selection.SpecialCells(xlCellTypeLastCell), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
Count = theOccurrence
Do Until Count < 2
Set c = Cells.FindNext(After:=Cells(c.Row, c.Column))
Count = Count - 1
Loop
If c Is Nothing Then
theStartRow = "Nothing"
Else
theStartRow = c.Row
End If
Set c = Nothing
StartRow = theStartRow
End Property


Public Property Get EndRow(sheetName, TextValue, Optional theOccurrence As Integer)
Set c = Sheets(sheetName).Cells.Find(What:=TextValue, After:=Selection.SpecialCells(xlCellTypeLastCell), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
Count = theOccurrence
Do Until Count < 2
Set c = Cells.FindNext(After:=Cells(c.Row, c.Column))
Count = Count - 1
Loop
If c Is Nothing Then
theEndRow = "Nothing"
Else
i = c.Row
j = c.Column
Do
i = i + 1
Loop Until Sheets(sheetName).Cells(i, j) = ""
theEndRow = i - 1
End If
Set c = Nothing
EndRow = theEndRow
End Property

I have similar code to get the StartCol and EndCol. Does anyone have a more efficent way to get the begining and ending row and column of the data on the sheet?

Thanks,

kcsmiles

 

[bertie]

See if this works.
Each worksheet has a "used range", which consists of cells containing values.
i.e., In the sample data below, the "used range" is "C6:E7"
Start Row = 6
End row = 7
Start Column = 3
End Column = 5

NB This was tested on Sheet2

Excel 2007
	C	D	E
6	x	x	x
7	x	x	x
Sheet2

Place the code below in a standard module, i.e., Insert => Module

Sub test02()
   Dim rng As Range
   Dim rw As Long
   Dim col As Long
   
   Set rng = Sheets("Sheet2").UsedRange
   
   rw = rng.Row
   MsgBox "Start Row: " & rw
   
   rw = rng.Row + rng.Rows.Count - 1
   MsgBox "End Row: " & rw
   
   col = rng.Column
   MsgBox "Start Column:" & col
   
   col = rng.Column + rng.Columns.Count - 1
   MsgBox "End Column: " & col
   
   'tidy up
   Set rng = Nothing
End Sub