473,327 Members | 1,892 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Getting the used Range of a worksheet...

1
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?
Sep 25 '08 #1
1 5609
Remove the public declaration for ws.
Note...
The name of a worksheet is a String not an Object.
Objects in Excel must be established using the "Set" statement.
'--
Sub Format_Pricer()
Dim ws As Worksheet
Set ws = Worksheets("All Items")
ws.Activate
MsgBox "The Used Range of this Worksheet is: " & GetUsedRange(ws).Address
End Sub
'--
Sep 25 '08 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: green fox | last post by:
I need to take the results of a access query (quite small) and export it into an excel worksheet-a range if possible. The worksheet is sort of a way station where I need to execute a simple sum...
2
by: deko | last post by:
I use a complied query to export to Excel like this: SELECT * INTO . FROM tblExcelData; But I have a situation where I need to export several tables into the same worksheet. The idea is to...
10
by: Maik | last post by:
Hello, I've got a problem with access to special ranges. I renamed some cells (for example "C4" in "CUSTOM.GENERAL.VPRIM"). This is necessary, because I have to read out only these cells in...
4
by: IMS.Rushikesh | last post by:
Hi All, I am trying to execute below code but it gives me an COMException ///// Code Start //// public string GetName(Excel.Range range) { try { if (range.Name != null)
3
by: | last post by:
I wrote a class in VB.NET to export the contents of a datagrid to Excel. It works perfectly on my machine, but it fails on my customers' PCs that have identical versions of Win XP (SP1) and Excel...
0
by: Robert Bravery | last post by:
HI All How can I get the data aschema from an excel worksheet. Keeping in mind that the actuall data might not be the first few rows, so actuall only start at row 60 or 100. I have set a name...
0
by: shantanu | last post by:
I am trying to convert a macro code to c# that will copy the values of a column and paste to anather through paste special. Everything is working fine but the transpose meathod to paste the column...
0
by: serhio | last post by:
Hello, I have a problem with Microsoft.Office.Interop.Excel.Range.Group() method. The problem is that I can call this method only 7 times, before it throws me an exception. I don't...
9
Catalyst159
by: Catalyst159 | last post by:
I have a form which is used to calculate residential Floor Area Ratio (FAR). The form is structured into seven parts as follows: Part A: Maximum FAR and Floor Area: Part B: Gross Floor Area of...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.