Need Help on selecting using vba on excel

[eddie_leo84]

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

[wild_bill]

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)

[eddie_leo84]

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.

[RobDog888]

Moved to VBA Forum

[Webtest]

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 edt

'========================================================================
' **********************************************************************
'
' 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
'

[Webtest]

ALERT: I modified the code (Bolded lines) in the previous post.

[eddie_leo84]

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

[Webtest]

What version of Excel are you running? This works fine for me in Excel '97 and 2002.

[eddie_leo84]

The version of excel i am using is 2000

[eddie_leo84]

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()

[Webtest]

Eddie:

A sample call using the currently Active Worksheet is right there in the original code ...

'    Dim myReturnedRange as Range
'    Dim mySheet as Worksheet
'    Set mySheet = ActiveSheet
'    Set myReturnedRange = ActualUsedRange(mySheet)
'    If myReturnedRange Is Nothing Then ...

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:

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

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.

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:

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))  '<<<<
    ...

Bad Newbie Vibes ... see next post ...
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!

[Webtest]

Hey ... I told you I was a Newbie! You CAN use a 'ByVal' reference to a Worksheet! The following works ...

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

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 ???

Humble Pie for lunch today for sure!

[eddie_leo84]

Sorry again, can i know what is the Application.CountA do? CountA issit a variable?

[RobDog888]

Actually countA is a worksheet function.

Counts the number of cells that are not empty and the values within the list of arguments. Use COUNTA to count the number of cells that contain data in a range or array.

Syntax

COUNTA(value1,value2,...)

Value1, value2, ... are 1 to 30 arguments representing the values you want to count. In this case, a value is any type of information, including empty text ("") but not including empty cells. If an argument is an array or reference, empty cells within the array or reference are ignored. If you do not need to count logical values, text, or error values, use the COUNT function.

HTH