Excel VBA - UsedRange problem
 

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(xlCellTypeLastCel l).Row
and the problem persists.

Any help ?

Thank you
Catali

--
Message posted from http://www.ExcelForum.com

Excel VBA - UsedRange problem
 

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(xlCellTypeLastCel l).Row
and the problem persists.

Any help ?

Thank you
Catalin


---
Message posted from http://www.ExcelForum.com/

Excel VBA - UsedRange problem
 

This is a known problem. Check here
http://www.contextures.com/xlfaqApp.html#Unused for details.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"cata_and " wrote in message
...
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(xlCellTypeLastCel l).Row
and the problem persists.

Any help ?

Thank you
Catalin


---
Message posted from http://www.ExcelForum.com/

Excel VBA - UsedRange problem
 

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
-----Original Message-----
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


---
Message posted from http://www.ExcelForum.com/


.

Excel VBA - UsedRange problem
 

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

--
Message posted from http://www.ExcelForum.com

Excel VBA - UsedRange problem
 

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


---
Message posted from http://www.ExcelForum.com/

Excel VBA - UsedRange problem
 

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

-----Original Message-----
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 know
how to use the function that Alan provide.

Can you be more specific Alan ? Thanks.

Regards,
Catalin


---
Message posted from http://www.ExcelForum.com/

.