Printable View
Currently i uses the range to select all information in the excel file that i open.
But i realize that the range is always changing. I need to select all and not the range. Any Ideas?
Public Sub SortRange1()
Dim range1 As Object
range1 = xlSheet.Range("A2:AO872")
range1.Sort(Key1:=range1.Columns(1), order1:=Excel.XlSortOrder.xlAscending)
End Sub
It's been a while, but I believe there is a usedrange property. If you don't need the data from every colomn, you can use .Range("A2:A" & usedrange.rows.count)
I need the data from every column, what happen now is my counter in the main program.
For count = 2 To 437
it's correct that it start counting from 2 but the ending rows. is different because there is different file and has different ending rows so how can i let it detect the last row in excel.
Moved to VBA Forum :)
The inspiration for this came from a forum, but I've lost the reference. Thanks be to whoever posted the original code. I hope this helps, even if you just use the standard "UsedRange" function.
MODIFIED 8/25/2005 9:47 AM edtCode:'========================================================================
' **********************************************************************
'
' FUNCTION "ActualUsedRange"
'
'This function returns the ACTUAL 'UsedRange' of cells from "A1" to the
'cell intersection of the last non-empty Row and the last non-empty Column.
'If the Sheet is empty, the function returns "Nothing" as the range.
'This function does NOT count any formatted but otherwise empty cells.
'Cells with only non-printing characters appear blank, but are actually not.
'This is a replacement for the buggy Excel 'UsedRange' function, which
'occasionally returns a range bigger than the actual Used Range. NOTE: This
'could take a LONG time to run!
'
'The return of "Nothing" can be detected in the calling code with the following:
' Dim myReturnedRange as Range
' Dim mySheet as Worksheet
' Set mySheet = ActiveSheet
' Set myReturnedRange = ActualUsedRange(mySheet)
' If myReturnedRange Is Nothing Then ...
'
' **********************************************************************
Function ActualUsedRange(anySht As Worksheet) As Range
Dim i As Long ' Loop Index
Dim c As Integer ' Column number
Dim r As Long ' Row number
'Use Excel 'UsedRange' as an inclusive estimate
With anySht.UsedRange
'Find the total number of COLUMNS
i = .Cells(.Cells.Count).Column
if i < 256 then i = i + 1
'Starting from the RIGHT, eliminate empty columns
For c = i To 1 Step -1
If Application.CountA(anySht.Columns(c)) > 0 Then Exit For
Next c
'Find the total number of ROWS
i = .Cells(.Cells.Count).Row
if i < 65536 then i = i + 1
'Starting from the BOTTOM, eliminate empty rows
For r = i To 1 Step -1
If Application.CountA(anySht.Rows(r)) > 0 Then Exit For
Next r
End With
'Set the return value with the ACTUAL range from $A$1 to the REAL 'Last Cell'
If (r = 0 And c = 0) Then
'Return 'Nothing' for an empty sheet
Set ActualUsedRange = Nothing
Else
'Return the Actual Used Range determined for this sheet
Set ActualUsedRange = anySht.Range(anySht.Cells(1, 1), anySht.Cells(r, c))
End If
End Function
'
ALERT: I modified the code (Bolded lines) in the previous post.
i put your code in my class, a few coding seem to me that it has error. like
both Application.CountA and anySht.Range(anySht.Cells(1, 1), anySht.Cells(r, c))
Thanks for the help, thanks alot
What version of Excel are you running? This works fine for me in Excel '97 and 2002.
The version of excel i am using is 2000
i used your code in my class file
Function ActualUsedRange(ByVal anySht As Excel.Worksheet) As Excel.Range()
Dim i As Long ' Loop Index
Dim c As Integer ' Column number
Dim r As Long ' Row number
'Use Excel 'UsedRange' as an inclusive estimate
With anySht.UsedRange
'Find the total number of COLUMNS
i = .Cells(.Cells.Count).Column
If i < 256 Then i = i + 1
'Starting from the RIGHT, eliminate empty columns
For c = i To 1 Step -1
If xlSheet.Application.CountA(anySht.Columns(c)) > 0 Then Exit For
Next c
'Find the total number of ROWS
i = .Cells(.Cells.Count).Row
If i < 65536 Then i = i + 1
'Starting from the BOTTOM, eliminate empty rows
For r = i To 1 Step -1
If xlSheet.Application.CountA(anySht.Rows(r)) > 0 Then Exit For
Next r
End With
'Set the return value with the ACTUAL range from $A$1 to the REAL 'Last Cell'
If (r = 0 And c = 0) Then
'Return 'Nothing' for an empty sheet
ActualUsedRange = Nothing
Else
'Return the Actual Used Range determined for this sheet
ActualUsedRange = anySht.excel.Range(anySht.Cells(1, 1), anySht.Cells(r, c))
End If
End Function
The question is how can i call the function in my main class, there is an error when i call it. With this line. appExcel is the class file i write the function. It say must pass in argument but wat argument should i really pass in?
appExcel.ActualUsedRange()
Eddie:
A sample call using the currently Active Worksheet is right there in the original code ...Also, PUT MY FUNCTION BACK THE WAY I ORIGINALLY PROVIDED IT!!! Play with this function in a new workbook. Record a new macro, and then just click the stop button. It should be Macro1 (or whatever). Edit the macro, taking everything out between the Sub() and End Sub lines, and insert the following:Code:' Dim myReturnedRange as Range
' Dim mySheet as Worksheet
' Set mySheet = ActiveSheet
' Set myReturnedRange = ActualUsedRange(mySheet)
' If myReturnedRange Is Nothing Then ...
Copy the ORIGINAL ActualUsedRange function right after the new Macro Sub, and at the very top of the Module, put "Option Explicit". Now play with it in a simple Macro to make sure it works BEFORE you try to cram it into your application.Code:Dim myReturnedRange as Range
Dim mySheet as Worksheet
Set mySheet = ActiveSheet
Set myReturnedRange = ActualUsedRange(mySheet)
If myReturnedRange Is Nothing Then
MsgBox "The Active Sheet is EMPTY!"
Else
MsgBox "The actual Used Range is " & myReturnedRange.Address
End If
This function runs correctly in Excel '97 and Excel 2002 ... I cut and pasted it right out of the Forum, but I had to back out some of your changes from the following 5 lines of code. Here are the CORRECTED lines:Bad Newbie Vibes ... see next post ...Code:Function ActualUsedRange(anySht As Excel.Worksheet) As Excel.Range
...
If Application.CountA(anySht.Columns(c)) > 0 Then Exit For '<<<
...
If Application.CountA(anySht.Rows(r)) > 0 Then Exit For '<<<
...
Set ActualUsedRange = Nothing '<<<
...
Set ActualUsedRange = anySht.Range(anySht.Cells(1, 1), anySht.Cells(r, c)) '<<<<
...
With respect to your changes to the Function statement ... You can NOT pass a reference to a sheet "ByVal" ... think about it ... What is the 'Value' of a Sheet? You want to examine the ACTUAL Sheet, so you need a direct reference to it ... not the 'Value' of it (whatever that means?).
Keep plugging away, but try to test things in small increments until you understand what is going on. Hey, I'm a newbie too, believe it or not!
Good luck and good learning!
Hey ... I told you I was a Newbie! You CAN use a 'ByVal' reference to a Worksheet! The following works ...You'll have to get someone better than I to explain the difference, but I suspect that 'ByVal' operates on a Copy of the sheet, where without it you are referencing the original sheet ???Code:Option Explicit
Sub Macro1()
JUNK(ActiveSheet).Select
End Sub
Function JUNK(ByVal aSheet As Worksheet) As Range
Set JUNK = aSheet.Range("C5:D7")
End Function
Humble Pie for lunch today for sure!
Sorry again, can i know what is the Application.CountA do? CountA issit a variable?
Actually countA is a worksheet function.
HTHQuote:
Originally Posted by Excel Helpfile