Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Cell
Hi All:
I have two questions that are sort of related. 1-I was wondering is anyone knows in excel, how we could know, the last row and column on the spreadhseet that has any data in it? 2- Let's say that you have data on the Range A1:A20, and A1000. At this point the vertical scroll bar is very small. Then if you delete A1000, is there a way to reset the limits on the vertical scroll bar? In the regular VB, you have Scrolbar.min and scrolbar.max. Is there such a thing in VBA for Excel? Thanks for your help. Bob |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Cell
not sure what you mean, but you can try debra's site. http://www.contextures.com/xlfaqApp.html#Unused -- Gary "Bob" wrote in message ... Hi All: I have two questions that are sort of related. 1-I was wondering is anyone knows in excel, how we could know, the last row and column on the spreadhseet that has any data in it? 2- Let's say that you have data on the Range A1:A20, and A1000. At this point the vertical scroll bar is very small. Then if you delete A1000, is there a way to reset the limits on the vertical scroll bar? In the regular VB, you have Scrolbar.min and scrolbar.max. Is there such a thing in VBA for Excel? Thanks for your help. Bob |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Cell
Perhaps you can make use of these two functions for your first question...
Function MaxRowInUse(Optional WS As Worksheet, Optional _ FactorInHiddenRows As Boolean = False) As Long Dim X As Long Dim LastRow As Long If WS Is Nothing Then Set WS = ActiveSheet With WS For X = 1 To .UsedRange.Columns.Count If Not (Not FactorInHiddenRows And Columns(X).Hidden) Then LastRow = .Cells(.Rows.Count, X).End(xlUp).Row If LastRow MaxRowInUse Then MaxRowInUse = LastRow End If Next End With End Function Function MaxColumnInUse(Optional WS As Worksheet, Optional _ FactorInHiddenColumns As Boolean = False) As Long Dim X As Long Dim LastColumn As Long If WS Is Nothing Then Set WS = ActiveSheet With WS For X = 1 To .UsedRange.Rows.Count If Not (Not FactorInHiddenColumns And Rows(X).Hidden) Then LastColumn = .Cells(X, .Columns.Count).End(xlToLeft).Column If LastColumn MaxColumnInUse Then MaxColumnInUse = LastColumn End If Next End With End Function Note: If you don't specify a worksheet in the first (optional) argument, then the active sheet is used. The second optional argument is the interesting one... it lets you determine whether to include hidden rows when determining the maximum row that is in use; that is, if a hidden row contains the maximum row, it will be ignored unless the second argument is set to True. This allows you to get the maximum row for what you see on the worksheet rather than for what any hidden data would return. I wasn't sure which would be the most logical default for this second argument, so I chose not factor in hidden rows (that is, the functions return the maximum row for only the visible data); if desired, this can be easily changed in the declaration headers for the function (change the False to True). -- Rick (MVP - Excel) "Bob" wrote in message ... Hi All: I have two questions that are sort of related. 1-I was wondering is anyone knows in excel, how we could know, the last row and column on the spreadhseet that has any data in it? 2- Let's say that you have data on the Range A1:A20, and A1000. At this point the vertical scroll bar is very small. Then if you delete A1000, is there a way to reset the limits on the vertical scroll bar? In the regular VB, you have Scrolbar.min and scrolbar.max. Is there such a thing in VBA for Excel? Thanks for your help. Bob |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Cell
Another one
Sub test() Dim nr As Long, nc As Long, cel As Range, s As String If LastDataCell(ActiveSheet, nr, nc, cel) Then s = "Last data cell " & cel.Address & vbCr & _ "row " & nr & vbCr & "col " & nc Else s = "empty sheet" End If MsgBox s End Sub Function LastDataCell(ws As Worksheet, _ lastRow As Long, lastCol As Long, _ Optional rCell As Range) As Boolean Dim X As Variant On Error Resume Next LastDataCell = True With ws lastRow = .Cells.Find("*", .Range("A1"), xlFormulas, _ xlPart, xlByRows, xlPrevious, 0).Row lastCol = .Cells.Find("*", .Range("A1"), xlFormulas, _ xlPart, xlByColumns, xlPrevious, 0).Column X = .Cells.Find("") 'reset Find If Err.Number < 0 Then ' an empty sheet lastRow = 1 lastCol = 1 LastDataCell = False End If Set rCell = .Cells(lastRow, lastCol) End With End Function To reset the scrollbars, clear any formats below/right of the last data cell, ie delete rows & columns. Then you'll probably need to call ws.usedrange to fully reset (it'll get done when you save, close, re-open) Regards, Peter T "Bob" wrote in message ... Hi All: I have two questions that are sort of related. 1-I was wondering is anyone knows in excel, how we could know, the last row and column on the spreadhseet that has any data in it? 2- Let's say that you have data on the Range A1:A20, and A1000. At this point the vertical scroll bar is very small. Then if you delete A1000, is there a way to reset the limits on the vertical scroll bar? In the regular VB, you have Scrolbar.min and scrolbar.max. Is there such a thing in VBA for Excel? Thanks for your help. Bob |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Cell
one way i've usde as long as i know i have headers for each column:
Sub test() Dim ws As Worksheet Dim lastcol As Long Dim lastrow As Long Dim arr As Variant Dim i As Long Set ws = Worksheets("Sheet1") lastcol = ws.Cells(1, Columns.Count).End(xlToLeft).Column ReDim arr(1 To lastcol) For i = 1 To lastcol arr(i) = ws.Cells(Rows.Count, i).End(xlUp).Row Next lastrow = Application.Max(arr) End Sub -- Gary "Bob" wrote in message ... Hi All: I have two questions that are sort of related. 1-I was wondering is anyone knows in excel, how we could know, the last row and column on the spreadhseet that has any data in it? 2- Let's say that you have data on the Range A1:A20, and A1000. At this point the vertical scroll bar is very small. Then if you delete A1000, is there a way to reset the limits on the vertical scroll bar? In the regular VB, you have Scrolbar.min and scrolbar.max. Is there such a thing in VBA for Excel? Thanks for your help. Bob |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Cell
Thanks Peter. Is there anyway of resetting the scrollbars without closing
the file? You see, Once I know the last row (or column), I use the rows under it for my own use. I basically, cut rows from above the last row, and paste them below it to reorder the rows (kind of like sorting, but I do not sort). Then once I have the rows in the new order, I cut them from below the last row and put them in the above the last row. So, if I do not change the scrollbar range, the user may get confused as what happened. So, I need to delete the new rows below the last row, and reset the scrollbar without closing the file. Thanks for your help. Bob "Peter T" <peter_t@discussions wrote in message ... Another one Sub test() Dim nr As Long, nc As Long, cel As Range, s As String If LastDataCell(ActiveSheet, nr, nc, cel) Then s = "Last data cell " & cel.Address & vbCr & _ "row " & nr & vbCr & "col " & nc Else s = "empty sheet" End If MsgBox s End Sub Function LastDataCell(ws As Worksheet, _ lastRow As Long, lastCol As Long, _ Optional rCell As Range) As Boolean Dim X As Variant On Error Resume Next LastDataCell = True With ws lastRow = .Cells.Find("*", .Range("A1"), xlFormulas, _ xlPart, xlByRows, xlPrevious, 0).Row lastCol = .Cells.Find("*", .Range("A1"), xlFormulas, _ xlPart, xlByColumns, xlPrevious, 0).Column X = .Cells.Find("") 'reset Find If Err.Number < 0 Then ' an empty sheet lastRow = 1 lastCol = 1 LastDataCell = False End If Set rCell = .Cells(lastRow, lastCol) End With End Function To reset the scrollbars, clear any formats below/right of the last data cell, ie delete rows & columns. Then you'll probably need to call ws.usedrange to fully reset (it'll get done when you save, close, re-open) Regards, Peter T "Bob" wrote in message ... Hi All: I have two questions that are sort of related. 1-I was wondering is anyone knows in excel, how we could know, the last row and column on the spreadhseet that has any data in it? 2- Let's say that you have data on the Range A1:A20, and A1000. At this point the vertical scroll bar is very small. Then if you delete A1000, is there a way to reset the limits on the vertical scroll bar? In the regular VB, you have Scrolbar.min and scrolbar.max. Is there such a thing in VBA for Excel? Thanks for your help. Bob |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Cell
Thanks guys.
"Bob" wrote in message ... Hi All: I have two questions that are sort of related. 1-I was wondering is anyone knows in excel, how we could know, the last row and column on the spreadhseet that has any data in it? 2- Let's say that you have data on the Range A1:A20, and A1000. At this point the vertical scroll bar is very small. Then if you delete A1000, is there a way to reset the limits on the vertical scroll bar? In the regular VB, you have Scrolbar.min and scrolbar.max. Is there such a thing in VBA for Excel? Thanks for your help. Bob |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Cell
Hi Rick. Thanks for your help. By the way, for your info, on my earlier
question on re-ordering rows from last week, I decided to cut and paste rows. For the more detail explanation see my reply to Peter's post below. Thanks. Bob "Rick Rothstein" wrote in message ... Perhaps you can make use of these two functions for your first question... Function MaxRowInUse(Optional WS As Worksheet, Optional _ FactorInHiddenRows As Boolean = False) As Long Dim X As Long Dim LastRow As Long If WS Is Nothing Then Set WS = ActiveSheet With WS For X = 1 To .UsedRange.Columns.Count If Not (Not FactorInHiddenRows And Columns(X).Hidden) Then LastRow = .Cells(.Rows.Count, X).End(xlUp).Row If LastRow MaxRowInUse Then MaxRowInUse = LastRow End If Next End With End Function Function MaxColumnInUse(Optional WS As Worksheet, Optional _ FactorInHiddenColumns As Boolean = False) As Long Dim X As Long Dim LastColumn As Long If WS Is Nothing Then Set WS = ActiveSheet With WS For X = 1 To .UsedRange.Rows.Count If Not (Not FactorInHiddenColumns And Rows(X).Hidden) Then LastColumn = .Cells(X, .Columns.Count).End(xlToLeft).Column If LastColumn MaxColumnInUse Then MaxColumnInUse = LastColumn End If Next End With End Function Note: If you don't specify a worksheet in the first (optional) argument, then the active sheet is used. The second optional argument is the interesting one... it lets you determine whether to include hidden rows when determining the maximum row that is in use; that is, if a hidden row contains the maximum row, it will be ignored unless the second argument is set to True. This allows you to get the maximum row for what you see on the worksheet rather than for what any hidden data would return. I wasn't sure which would be the most logical default for this second argument, so I chose not factor in hidden rows (that is, the functions return the maximum row for only the visible data); if desired, this can be easily changed in the declaration headers for the function (change the False to True). -- Rick (MVP - Excel) "Bob" wrote in message ... Hi All: I have two questions that are sort of related. 1-I was wondering is anyone knows in excel, how we could know, the last row and column on the spreadhseet that has any data in it? 2- Let's say that you have data on the Range A1:A20, and A1000. At this point the vertical scroll bar is very small. Then if you delete A1000, is there a way to reset the limits on the vertical scroll bar? In the regular VB, you have Scrolbar.min and scrolbar.max. Is there such a thing in VBA for Excel? Thanks for your help. Bob |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Cell
Have you tried doing simply theSheet.usedrange as I suggested previously
(after clearing formats in rows & col's below & right of last data cell). Regards, Peter T "Bob" wrote in message ... Thanks Peter. Is there anyway of resetting the scrollbars without closing the file? You see, Once I know the last row (or column), I use the rows under it for my own use. I basically, cut rows from above the last row, and paste them below it to reorder the rows (kind of like sorting, but I do not sort). Then once I have the rows in the new order, I cut them from below the last row and put them in the above the last row. So, if I do not change the scrollbar range, the user may get confused as what happened. So, I need to delete the new rows below the last row, and reset the scrollbar without closing the file. Thanks for your help. Bob "Peter T" <peter_t@discussions wrote in message ... Another one Sub test() Dim nr As Long, nc As Long, cel As Range, s As String If LastDataCell(ActiveSheet, nr, nc, cel) Then s = "Last data cell " & cel.Address & vbCr & _ "row " & nr & vbCr & "col " & nc Else s = "empty sheet" End If MsgBox s End Sub Function LastDataCell(ws As Worksheet, _ lastRow As Long, lastCol As Long, _ Optional rCell As Range) As Boolean Dim X As Variant On Error Resume Next LastDataCell = True With ws lastRow = .Cells.Find("*", .Range("A1"), xlFormulas, _ xlPart, xlByRows, xlPrevious, 0).Row lastCol = .Cells.Find("*", .Range("A1"), xlFormulas, _ xlPart, xlByColumns, xlPrevious, 0).Column X = .Cells.Find("") 'reset Find If Err.Number < 0 Then ' an empty sheet lastRow = 1 lastCol = 1 LastDataCell = False End If Set rCell = .Cells(lastRow, lastCol) End With End Function To reset the scrollbars, clear any formats below/right of the last data cell, ie delete rows & columns. Then you'll probably need to call ws.usedrange to fully reset (it'll get done when you save, close, re-open) Regards, Peter T "Bob" wrote in message ... Hi All: I have two questions that are sort of related. 1-I was wondering is anyone knows in excel, how we could know, the last row and column on the spreadhseet that has any data in it? 2- Let's say that you have data on the Range A1:A20, and A1000. At this point the vertical scroll bar is very small. Then if you delete A1000, is there a way to reset the limits on the vertical scroll bar? In the regular VB, you have Scrolbar.min and scrolbar.max. Is there such a thing in VBA for Excel? Thanks for your help. Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing Options | Excel Discussion (Misc queries) | |||
Populate a cell if values in cell 1 and cell 2 match cell 3 and 4 | Excel Worksheet Functions | |||
Populate a cell if values in cell 1 and cell 2 match cell 3 and 4 | Excel Programming | |||
NEED VBA TO SELECT A CELL; NOTE THE CELL VALUE;COPYADJ CELL;FIND CELL VALUE IN A RANGE AND SO ON | Excel Programming | |||
How to create/run "cell A equals Cell B put Cell C info in Cell D | Excel Discussion (Misc queries) |