gwarcher
asked on
Excel VBA Range reference error
I'm having trouble setting a searchrange. I need to search through the entire worksheet and I have a parameter for searchrange. Any help would be great.
'get range
Dim LastCell As Range
Dim SearchRange as Range
Dim ColID As String
Dim lastCol As String
Dim lastRow As Long
Set LastCell = sheet.Cells.Find(what:="*" , after:=sheet.Cells(1, 1), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPreviou s, MatchCase:=False)
ColID = LastCell.Address
lastCol = Left(ColID, InStr(2, ColID, "$") - 1)
lastCol = Replace(lastCol, "$", "")
lastRow = CLng(LastCell.Row)
Set SearchRange = Range("A1:" & lastCol & ":" & lastRow)
'get range
Dim LastCell As Range
Dim SearchRange as Range
Dim ColID As String
Dim lastCol As String
Dim lastRow As Long
Set LastCell = sheet.Cells.Find(what:="*"
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPreviou
ColID = LastCell.Address
lastCol = Left(ColID, InStr(2, ColID, "$") - 1)
lastCol = Replace(lastCol, "$", "")
lastRow = CLng(LastCell.Row)
Set SearchRange = Range("A1:" & lastCol & ":" & lastRow)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Cleaner:
'get range
Dim LastCell As Range
Dim SearchRange As Range
Set LastCell = Sheet.Cells.Find(what:="*" , after:=Sheet.Cells(1, 1), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPreviou s, MatchCase:=False)
If Not LastCell Is Nothing Then
Set SearchRange = Sheet.Range("A1", LastCell)
End If
Or, if you know there will always be a value on the sheet:
'get range
Dim LastCell As Range
Dim SearchRange As Range
Set LastCell = Sheet.Cells.Find(what:="*" , after:=Sheet.Cells(1, 1), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPreviou s, MatchCase:=False)
Set SearchRange = Sheet.Range("A1", LastCell)
Kevin
'get range
Dim LastCell As Range
Dim SearchRange As Range
Set LastCell = Sheet.Cells.Find(what:="*"
If Not LastCell Is Nothing Then
Set SearchRange = Sheet.Range("A1", LastCell)
End If
Or, if you know there will always be a value on the sheet:
'get range
Dim LastCell As Range
Dim SearchRange As Range
Set LastCell = Sheet.Cells.Find(what:="*"
Set SearchRange = Sheet.Range("A1", LastCell)
Kevin
ASKER