I have troule with this macro... geting the used rsnge...
Public strName As String, ws As Worksheet
Sub Test()
Workbooks.Open Filename:= _
"C:\Documents and Settings\User\Desktop\IT Development\Pricer.xls"
'Begins formatting the pricer for generating the EPB...
strName = ActiveSheet.Name
'MsgBox strName
MsgBox "The Used Range of this Worksheet is: " & GetUsedRange(ws)
End Sub
Sub Format_Pricer()
Dim ws As Worksheet
Worksheets("All Items").Activate
ws = strName
MsgBox "The Used Range of this Worksheet is: " & GetUsedRange(ws)
End Sub
Public Function GetUsedRange(ws As Worksheet) As Range
' Assumes that Excel's UsedRange gives a superset
' of the real used range.
Dim s As String, x As Integer
Dim rng As Range
Dim r1Fixed As Integer, c1Fixed As Integer
Dim r2Fixed As Integer, c2Fixed As Integer
Dim i As Integer
Dim r1 As Integer, c1 As Integer
Dim r2 As Integer, c2 As Integer
Set GetUsedRange = Nothing
' Start with Excel's used range
Set rng = ws.UsedRange
' Get bounding cells for Excel's used range
' That is, Cells(r1,c1) to Cells(r2,c2)
r1 = rng.Row
r2 = rng.Rows.Count + r1 - 1
c1 = rng.Column
c2 = rng.Columns.Count + c1 - 1
' Save existing values
r1Fixed = r1
c1Fixed = c1
r2Fixed = r2
c2Fixed = c2
' Check rows from top down for all blanks.
' If found, shrink rows.
For i = 1 To r2Fixed - r1Fixed + 1
If Application.CountA(rng.Rows(i)) = 0 Then
' empty row -- reduce
r1 = r1 + 1
Else
' nonempty row, get out
Exit For
End If
Next
' Repeat for columns from left to right
For i = 1 To c2Fixed - c1Fixed + 1
If Application.CountA(rng.Columns(i)) = 0 Then
c1 = c1 + 1
Else
Exit For
End If
Next
' Reset the range
Set rng = _
ws.Range(ws.Cells(r1, c1), ws.Cells(r2, c2))
' Start again
r1Fixed = r1
c1Fixed = c1
r2Fixed = r2
c2Fixed = c2
' Do rows from bottom up
For i = r2Fixed - r1Fixed + 1 To 1 Step -1
If Application.CountA(rng.Rows(i)) = 0 Then
r2 = r2 - 1
Else
Exit For
End If
Next
' Repeat for columns from right to left
For i = c2Fixed - c1Fixed + 1 To 1 Step -1
If Application.CountA(rng.Columns(i)) = 0 Then
c2 = c2 - 1
Else
Exit For
End If
Next
Set GetUsedRange = _
ws.Range(ws.Cells(r1, c1), ws.Cells(r2, c2))
End Function
I am opening a workbook with one worksheet named "All Items". At this line in the function I get the error "Objct variable or eith block variable not set". Can anyone guide me?