Excel VBA - UsedRange problem

  • Thread starter Thread starter cata_and
  • Start date Start date
C

cata_and

Hello Everybody !

Please help me with UsedRange property.

I have a spreadsheet for which I need to know the last used row but
get a wrong result every time I run the macro.

I use this line:

Worksheets(i).UsedRange.Rows.Count

which returns 434 but in reality are 343.

I tried also with

Worksheets(1).Cells.SpecialCells(xlCellTypeLastCell).Row
and the problem persists.

Any help ?

Thank you
Catali
 
Hi Catalin,

I don't suppose there are any hidden rows in the spread sheet?

Cheers,
Foss

----- cata_and > wrote: -----

Hello Everybody !

Please help me with UsedRange property.

I have a spreadsheet for which I need to know the last used row but I
get a wrong result every time I run the macro.

I use this line:

Worksheets(i).UsedRange.Rows.Count

which returns 434 but in reality are 343.

I tried also with

Worksheets(1).Cells.SpecialCells(xlCellTypeLastCell).Row
and the problem persists.

Any help ?

Thank you
Catalin
 
You could try this - I've always found that it works

'
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++
' Function: Goto_Last
' Author : Alan Hutchins (amended from original by Rob
Bovey)
' Date : 23rd December 2003
' Purpose : Find the Last cell in a worksheet and return
the address
'
'
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++
' Find Last Cell in worksheet
Function Goto_Last(strRange, int_col, int_row)
On Error Resume Next
Application.ScreenUpdating = False
Cells(Cells.Find("*", Range("A1"), , , xlByRows,
xlPrevious).Row, _
Cells.Find("*", Range("A1"), , , xlByColumns,
xlPrevious).column).Select
If Err.Number <> 0 Then MsgBox "No data in sheet"
Application.ScreenUpdating = True
strRange = ActiveCell.AddressLocal
int_col = ActiveCell.column
int_row = ActiveCell.Row
End Function
 
Hi again,

Thank you all for your kind support.

First, there is no hidden rows or columns in the sheet.
Second, I have to mention that I am a newbie and I do not really kno
how to use the function that Alan provide.

Can you be more specific Alan ? Thanks.

Regards,
Catali
 
UsedRange is unreliable, as is SpecialCells LastCell. Try this:

Dim LR As Long
LR = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows,
SearchDirection:=xlPrevious).Row
MsgBox LR
 
It's quite easy.

Example

Sub example()
Dim strRange As String
Dim int_col As Integer
Dim int_row As Integer
Dim x As String

strRange = "A1"
int_col = 0
int_row = 0

Sheets("Sheet1").Select

x = Goto_Last(strRange, int_col, int_row)

MsgBox strRange
MsgBox int_col
MsgBox int_row

End Sub

Enter any value in sheet1 at a random cell, then run the
example.

Msgbox is only used so that you can see the results on
screen to help you

HTH
 
Back
Top