Below is the function I am trying to use so I can determine the UsedRange of a worksheet:
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.Row
s(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.Col
umns(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.Row
s(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.Col
umns(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 keep getting a syntax error in the code:
MsgBox "The Used Range of this Worksheet is: " & GetUsedRange (ws As Worksheet)
What is wrong
Start Free Trial
