Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 BUG UsedRange/LastCell differences with Excel2003. | Excel Discussion (Misc queries) | |||
Problem with UsedRange.Rows.Count | Excel Programming | |||
UsedRange problem | Excel Programming | |||
Help - How do I reset usedrange in excel 2000 | Excel Programming | |||
UsedRange problem | Excel Programming |