Last Cell

[Bob]

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

 

[Gary Keramidas]

not sure what you mean, but you can try debra's site.

http://www.contextures.com/xlfaqApp.html#Unused

 

[Rick Rothstein]

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).

 

[Peter T]

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

 

[Gary Keramidas]

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

 

[Bob]

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

 

[Bob]

Thanks guys.

 

[Bob]

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

 

[Peter T]

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