Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining the last row and column used on a sheet
How can I quickly find the last used row and column of a worksheet?
I appreciate your help, -John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining the last row and column used on a sheet
Use code like Dim LastCell As Range Dim WS As Worksheet Set WS = ActiveSheet Set LastCell = WS.UsedRange.SpecialCells(xlCellTypeLastCell) Debug.Print LastCell.Address Note, though, that this LastCell may not contain any data. It is in the last row that has any data in it and in the last column that has any data in it. E.g., X X X X X LC Where X is data and LC is the LastCell, which may be empty. If you need the last cell that has data, you need to decide whether you want the right-most column or the highest-number row. For example, X X X X X 11 X 22 In this example, is 11 or 22 the last cell? It depends on what you are looking for and what you need to accomplish with the last cell. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Mon, 23 Feb 2009 09:19:02 -0800, John wrote: How can I quickly find the last used row and column of a worksheet? I appreciate your help, -John |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining the last row and column used on a sheet
Give these a try...
LastUsedRow = ActiveSheet.Cells.Find(What:="*", _ SearchDirection:=xlPrevious, _ SearchOrder:=xlRows).Row LastUsedColumn = ActiveSheet.Cells.Find(What:="*", _ SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column -- Rick (MVP - Excel) "John" wrote in message ... How can I quickly find the last used row and column of a worksheet? I appreciate your help, -John |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining the last row and column used on a sheet
Be careful with UsedRange. The range inside gets expanded just by
talking to the cells even if there's nothing in it. I always use .Find as a result. Maury |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining the last row and column used on a sheet
This being the case, how does one "delete the last row, column entry" if a
cell was used erroneously? I keep getting a compatibilty error in 2007 from a previous version, where the cell used was "outside" 255 X 65535. "Maury Markowitz" wrote: Be careful with UsedRange. The range inside gets expanded just by talking to the cells even if there's nothing in it. I always use .Find as a result. Maury |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining the last row and column used on a sheet
The way to avoid trying to do something with a blank cell, that you think
has data, is to test it with an If...Then statement. If it is not empty, equal to zero or null then do something with it, else do nothiing. Recognizing that xl2007 has considerably more rows and columns than previous versions, all code from previous versions should be reviewed for possible errors related to those differences. "Terry" wrote in message ... This being the case, how does one "delete the last row, column entry" if a cell was used erroneously? I keep getting a compatibilty error in 2007 from a previous version, where the cell used was "outside" 255 X 65535. "Maury Markowitz" wrote: Be careful with UsedRange. The range inside gets expanded just by talking to the cells even if there's nothing in it. I always use .Find as a result. Maury |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining the last row and column used on a sheet
Well I ran some code to delete any/all(hopefully) outside the normal area,
but still get the compatibility warning. After checking the last row/column again now I find blank cells. "JLGWhiz" wrote: The way to avoid trying to do something with a blank cell, that you think has data, is to test it with an If...Then statement. If it is not empty, equal to zero or null then do something with it, else do nothiing. Recognizing that xl2007 has considerably more rows and columns than previous versions, all code from previous versions should be reviewed for possible errors related to those differences. "Terry" wrote in message ... This being the case, how does one "delete the last row, column entry" if a cell was used erroneously? I keep getting a compatibilty error in 2007 from a previous version, where the cell used was "outside" 255 X 65535. "Maury Markowitz" wrote: Be careful with UsedRange. The range inside gets expanded just by talking to the cells even if there's nothing in it. I always use .Find as a result. Maury . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining the last row and column used on a sheet
I am not running xl2007 so I am not familiar with the alerts unless they
apply to xl2003 as well. I don't recall seeing one in xl2003 regarding compatibility. What exactly does it say and does the associated help file provide any further information on possible causes? Also, how is a normal area defined? I am having trouble visualizing the problem. "Terry" wrote in message ... Well I ran some code to delete any/all(hopefully) outside the normal area, but still get the compatibility warning. After checking the last row/column again now I find blank cells. "JLGWhiz" wrote: The way to avoid trying to do something with a blank cell, that you think has data, is to test it with an If...Then statement. If it is not empty, equal to zero or null then do something with it, else do nothiing. Recognizing that xl2007 has considerably more rows and columns than previous versions, all code from previous versions should be reviewed for possible errors related to those differences. "Terry" wrote in message ... This being the case, how does one "delete the last row, column entry" if a cell was used erroneously? I keep getting a compatibilty error in 2007 from a previous version, where the cell used was "outside" 255 X 65535. "Maury Markowitz" wrote: Be careful with UsedRange. The range inside gets expanded just by talking to the cells even if there's nothing in it. I always use .Find as a result. Maury . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining the last row and column used on a sheet
Column A-T rows 1-33, the error goes like this:
Some cells or stlyes in this workbook contain formatting that is not supported by the selected file format(97-2003). These formats will be converted to closest format available. The help file discusses cells the old size 256 X 65536 and many other topics. After using the code to eliminate entries "WAY" outside find now the "last" cell AT42. No other format, style or formula looks out of what 97 used to contain. There are 150 sheets in the file. I hope this better explains what I have. "JLGWhiz" wrote: I am not running xl2007 so I am not familiar with the alerts unless they apply to xl2003 as well. I don't recall seeing one in xl2003 regarding compatibility. What exactly does it say and does the associated help file provide any further information on possible causes? Also, how is a normal area defined? I am having trouble visualizing the problem. "Terry" wrote in message ... Well I ran some code to delete any/all(hopefully) outside the normal area, but still get the compatibility warning. After checking the last row/column again now I find blank cells. "JLGWhiz" wrote: The way to avoid trying to do something with a blank cell, that you think has data, is to test it with an If...Then statement. If it is not empty, equal to zero or null then do something with it, else do nothiing. Recognizing that xl2007 has considerably more rows and columns than previous versions, all code from previous versions should be reviewed for possible errors related to those differences. "Terry" wrote in message ... This being the case, how does one "delete the last row, column entry" if a cell was used erroneously? I keep getting a compatibilty error in 2007 from a previous version, where the cell used was "outside" 255 X 65535. "Maury Markowitz" wrote: Be careful with UsedRange. The range inside gets expanded just by talking to the cells even if there's nothing in it. I always use .Find as a result. Maury . . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining the last row and column used on a sheet
I do not run xl2007 but here is a site that might help you.
http://blogs.msdn.com/excel/archive/...11/694891.aspx "Terry" wrote in message ... Column A-T rows 1-33, the error goes like this: Some cells or stlyes in this workbook contain formatting that is not supported by the selected file format(97-2003). These formats will be converted to closest format available. The help file discusses cells the old size 256 X 65536 and many other topics. After using the code to eliminate entries "WAY" outside find now the "last" cell AT42. No other format, style or formula looks out of what 97 used to contain. There are 150 sheets in the file. I hope this better explains what I have. "JLGWhiz" wrote: I am not running xl2007 so I am not familiar with the alerts unless they apply to xl2003 as well. I don't recall seeing one in xl2003 regarding compatibility. What exactly does it say and does the associated help file provide any further information on possible causes? Also, how is a normal area defined? I am having trouble visualizing the problem. "Terry" wrote in message ... Well I ran some code to delete any/all(hopefully) outside the normal area, but still get the compatibility warning. After checking the last row/column again now I find blank cells. "JLGWhiz" wrote: The way to avoid trying to do something with a blank cell, that you think has data, is to test it with an If...Then statement. If it is not empty, equal to zero or null then do something with it, else do nothiing. Recognizing that xl2007 has considerably more rows and columns than previous versions, all code from previous versions should be reviewed for possible errors related to those differences. "Terry" wrote in message ... This being the case, how does one "delete the last row, column entry" if a cell was used erroneously? I keep getting a compatibilty error in 2007 from a previous version, where the cell used was "outside" 255 X 65535. "Maury Markowitz" wrote: Be careful with UsedRange. The range inside gets expanded just by talking to the cells even if there's nothing in it. I always use .Find as a result. Maury . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Determining the nth Column | Excel Programming | |||
Programmatically determining CODE NAME for sheet based upon Sheet | Excel Programming | |||
Determining Column to Use | Excel Programming | |||
Determining Column to Use | Excel Programming | |||
determining last column | Excel Programming |