Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UsedRange and Ctrl-Shift-End Mystery
PUZZLE OF THE DAY: I have an Excel file with a worksheet where columns A:D
(only) are populated with a variable amount of data -- could be three rows, could be a thousand. For this discussion, let's say there are 50 rows. When I select A1 and then hit Ctrl-Shift-End, I would expect A1:D50 to be selected, but I get A1:IV50. I've tried selecting columns E:IV and deleting them entirely to supposedly reset the used range, but get the same result when I hit Ctrl-Shift-End. To make it even more puzzling, if I copy the data from A1:D50 into a new worksheet in the same workbook, Ctrl-Shift-End works as expected. And finally, I wrote a little code snippet to show me ActiveWorkbook.Sheets("nameofsheet").UsedRange.Col umns.Count, and it returns 4 in BOTH worksheets. Who can suggest why this is happening (and ideally, a way to fix it programmatically)? It's screwing me up when I try to import the weird worksheet into MSAccess. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UsedRange and Ctrl-Shift-End Mystery
i just check a mock sheet trying selecting coulumns e:iv and going to format
and reseting to general. "LarryP" wrote: PUZZLE OF THE DAY: I have an Excel file with a worksheet where columns A:D (only) are populated with a variable amount of data -- could be three rows, could be a thousand. For this discussion, let's say there are 50 rows. When I select A1 and then hit Ctrl-Shift-End, I would expect A1:D50 to be selected, but I get A1:IV50. I've tried selecting columns E:IV and deleting them entirely to supposedly reset the used range, but get the same result when I hit Ctrl-Shift-End. To make it even more puzzling, if I copy the data from A1:D50 into a new worksheet in the same workbook, Ctrl-Shift-End works as expected. And finally, I wrote a little code snippet to show me ActiveWorkbook.Sheets("nameofsheet").UsedRange.Col umns.Count, and it returns 4 in BOTH worksheets. Who can suggest why this is happening (and ideally, a way to fix it programmatically)? It's screwing me up when I try to import the weird worksheet into MSAccess. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UsedRange and Ctrl-Shift-End Mystery
Select those empty rows again and go to Format-Conditional Formatting.
Select Delete and check all three condistions. Click OK. OK again. Now try it. Regards, Paul -- "LarryP" wrote in message ... PUZZLE OF THE DAY: I have an Excel file with a worksheet where columns A:D (only) are populated with a variable amount of data -- could be three rows, could be a thousand. For this discussion, let's say there are 50 rows. When I select A1 and then hit Ctrl-Shift-End, I would expect A1:D50 to be selected, but I get A1:IV50. I've tried selecting columns E:IV and deleting them entirely to supposedly reset the used range, but get the same result when I hit Ctrl-Shift-End. To make it even more puzzling, if I copy the data from A1:D50 into a new worksheet in the same workbook, Ctrl-Shift-End works as expected. And finally, I wrote a little code snippet to show me ActiveWorkbook.Sheets("nameofsheet").UsedRange.Col umns.Count, and it returns 4 in BOTH worksheets. Who can suggest why this is happening (and ideally, a way to fix it programmatically)? It's screwing me up when I try to import the weird worksheet into MSAccess. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UsedRange and Ctrl-Shift-End Mystery
Nope, same result. I set E:IV cell format to General, but Ctrl-Shift-End
still selects A1:IV50. "jhyatt" wrote: i just check a mock sheet trying selecting coulumns e:iv and going to format and reseting to general. "LarryP" wrote: PUZZLE OF THE DAY: I have an Excel file with a worksheet where columns A:D (only) are populated with a variable amount of data -- could be three rows, could be a thousand. For this discussion, let's say there are 50 rows. When I select A1 and then hit Ctrl-Shift-End, I would expect A1:D50 to be selected, but I get A1:IV50. I've tried selecting columns E:IV and deleting them entirely to supposedly reset the used range, but get the same result when I hit Ctrl-Shift-End. To make it even more puzzling, if I copy the data from A1:D50 into a new worksheet in the same workbook, Ctrl-Shift-End works as expected. And finally, I wrote a little code snippet to show me ActiveWorkbook.Sheets("nameofsheet").UsedRange.Col umns.Count, and it returns 4 in BOTH worksheets. Who can suggest why this is happening (and ideally, a way to fix it programmatically)? It's screwing me up when I try to import the weird worksheet into MSAccess. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
UsedRange and Ctrl-Shift-End Mystery
You deleted the columns E through IV. You need to save the book however to
reset the used range. Note that the used range is not always what you would think it is so you want to be careful using it in code... Here is some code to find the bottom right most cell without refering to the used range... Public Function LastCell(Optional ByVal wks As Worksheet) As Range Dim lngLastRow As Long Dim intLastColumn As Integer If wks Is Nothing Then Set wks = ActiveSheet On Error Resume Next lngLastRow = wks.Cells.Find(What:="*", _ After:=wks.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row intLastColumn = wks.Cells.Find(What:="*", _ After:=wks.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 If lngLastRow = 0 Then lngLastRow = 1 intLastColumn = 1 End If Set LastCell = wks.Cells(lngLastRow, intLastColumn) End Function -- HTH... Jim Thomlinson "LarryP" wrote: PUZZLE OF THE DAY: I have an Excel file with a worksheet where columns A:D (only) are populated with a variable amount of data -- could be three rows, could be a thousand. For this discussion, let's say there are 50 rows. When I select A1 and then hit Ctrl-Shift-End, I would expect A1:D50 to be selected, but I get A1:IV50. I've tried selecting columns E:IV and deleting them entirely to supposedly reset the used range, but get the same result when I hit Ctrl-Shift-End. To make it even more puzzling, if I copy the data from A1:D50 into a new worksheet in the same workbook, Ctrl-Shift-End works as expected. And finally, I wrote a little code snippet to show me ActiveWorkbook.Sheets("nameofsheet").UsedRange.Col umns.Count, and it returns 4 in BOTH worksheets. Who can suggest why this is happening (and ideally, a way to fix it programmatically)? It's screwing me up when I try to import the weird worksheet into MSAccess. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
UsedRange and Ctrl-Shift-End Mystery
Thanks, Jim (and others who responded). My immediate problem is solved, but
Jim, your reply leads me off on a tangent. What does your function do that UsedRange does not do -- i.e., in what situation will your function give a right answer where UsedRange won't? Is this about UsedRange counting cells that have been formatted even if they are blank? "Jim Thomlinson" wrote: You deleted the columns E through IV. You need to save the book however to reset the used range. Note that the used range is not always what you would think it is so you want to be careful using it in code... Here is some code to find the bottom right most cell without refering to the used range... Public Function LastCell(Optional ByVal wks As Worksheet) As Range Dim lngLastRow As Long Dim intLastColumn As Integer If wks Is Nothing Then Set wks = ActiveSheet On Error Resume Next lngLastRow = wks.Cells.Find(What:="*", _ After:=wks.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row intLastColumn = wks.Cells.Find(What:="*", _ After:=wks.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 If lngLastRow = 0 Then lngLastRow = 1 intLastColumn = 1 End If Set LastCell = wks.Cells(lngLastRow, intLastColumn) End Function -- HTH... Jim Thomlinson "LarryP" wrote: PUZZLE OF THE DAY: I have an Excel file with a worksheet where columns A:D (only) are populated with a variable amount of data -- could be three rows, could be a thousand. For this discussion, let's say there are 50 rows. When I select A1 and then hit Ctrl-Shift-End, I would expect A1:D50 to be selected, but I get A1:IV50. I've tried selecting columns E:IV and deleting them entirely to supposedly reset the used range, but get the same result when I hit Ctrl-Shift-End. To make it even more puzzling, if I copy the data from A1:D50 into a new worksheet in the same workbook, Ctrl-Shift-End works as expected. And finally, I wrote a little code snippet to show me ActiveWorkbook.Sheets("nameofsheet").UsedRange.Col umns.Count, and it returns 4 in BOTH worksheets. Who can suggest why this is happening (and ideally, a way to fix it programmatically)? It's screwing me up when I try to import the weird worksheet into MSAccess. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
UsedRange and Ctrl-Shift-End Mystery
The difference is that my function will alway return the last cell in the
range that contains values. It looks backwards from Cell A1 to find the last used row with something in it and the last column with something in it. It then creates a range object from that row and column address. Used range is correct in most cases but not always. What you think of as cells is really just a bunch of place holders that can hold cell objects. Most of those objects have not been created. When you place a value (or formula or???) in them then they get created. When you delete the value (or ???) they usually get destroyed. If they don't get destroyed for some reason then your used range is going to be wrong. By deleteing the range with the errant cells you delete the object but don't destroy it. For the destructor to run you need to save the sheet (depending on your version of XL). I am not sure that this is the best explanation but hopefully you get the jist of it... -- HTH... Jim Thomlinson "LarryP" wrote: Thanks, Jim (and others who responded). My immediate problem is solved, but Jim, your reply leads me off on a tangent. What does your function do that UsedRange does not do -- i.e., in what situation will your function give a right answer where UsedRange won't? Is this about UsedRange counting cells that have been formatted even if they are blank? "Jim Thomlinson" wrote: You deleted the columns E through IV. You need to save the book however to reset the used range. Note that the used range is not always what you would think it is so you want to be careful using it in code... Here is some code to find the bottom right most cell without refering to the used range... Public Function LastCell(Optional ByVal wks As Worksheet) As Range Dim lngLastRow As Long Dim intLastColumn As Integer If wks Is Nothing Then Set wks = ActiveSheet On Error Resume Next lngLastRow = wks.Cells.Find(What:="*", _ After:=wks.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row intLastColumn = wks.Cells.Find(What:="*", _ After:=wks.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 If lngLastRow = 0 Then lngLastRow = 1 intLastColumn = 1 End If Set LastCell = wks.Cells(lngLastRow, intLastColumn) End Function -- HTH... Jim Thomlinson "LarryP" wrote: PUZZLE OF THE DAY: I have an Excel file with a worksheet where columns A:D (only) are populated with a variable amount of data -- could be three rows, could be a thousand. For this discussion, let's say there are 50 rows. When I select A1 and then hit Ctrl-Shift-End, I would expect A1:D50 to be selected, but I get A1:IV50. I've tried selecting columns E:IV and deleting them entirely to supposedly reset the used range, but get the same result when I hit Ctrl-Shift-End. To make it even more puzzling, if I copy the data from A1:D50 into a new worksheet in the same workbook, Ctrl-Shift-End works as expected. And finally, I wrote a little code snippet to show me ActiveWorkbook.Sheets("nameofsheet").UsedRange.Col umns.Count, and it returns 4 in BOTH worksheets. Who can suggest why this is happening (and ideally, a way to fix it programmatically)? It's screwing me up when I try to import the weird worksheet into MSAccess. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
F9, Shift+F9, Ctrl+Alt+F9 etc | Excel Discussion (Misc queries) | |||
Ctrl-Alt-Shift-F9 | Excel Programming | |||
Formula For CTRL+SHIFT+; | Excel Programming | |||
CTRL+SHIFT+END | Excel Programming | |||
What does CTRL-ALT-SHIFT-F4 do? | Excel Programming |