How to get the last valid (non blank) cell index?

[yajiv.vijay]

I have data in 100 rows through 120 colunms. How can i get the index
100 and 120? I mean last valid data index.

 

[Gary''s Student]

Let's assume that your data is Activesheet.UsedRange:

Set r = ActiveSheet.UsedRange

nLastRow = r.Rows.Count + r.Row - 1
MsgBox ("last row " & nLastRow)

nLastColumn = r.Columns.Count + r.Column - 1
MsgBox ("last column " & nLastColumn)

nFirstRow = r.Row
MsgBox ("first row " & nFirstRow)

nFirstColumn = r.Column
MsgBox ("first column " & nFirstColumn)

 

[Mike H]

you can return the adddress of the last used cell with this function

Function lastused()
lastused = Cells.Find(What:="*", After:=[A1], _
SearchDirection:=xlPrevious).Address
End Function

You call it with =lastused()

Mike

 

[Bill Martin]

I have data in 100 rows through 120 colunms. How can i get the index
100 and 120? I mean last valid data index.

--------------------

And of course in addition to what you've been told, the approach I
generally default to is:

LastRow = cells(TopLeftRow, TopLeftCol).end(xlDown).row
LastCol = cells(TopLeftRow, TopLeftCol).end(xlToRight).col

or if you prefer a different format:

LastRow = range("A1").end(xlDown).row

Good luck...

Bill

 

[Rick Rothstein \(MVP - VB\)]

I have data in 100 rows through 120 colunms. How can i get

the index 100 and 120? I mean last valid data index.

Perhaps the subroutine listed after my signature is what you are after. You
would use it like this. Declare four variables (for the top row, bottom row,
left column and right column numbers). Here is a sample subroutine showing
how to use it...

Sub Test()
Dim TopRow As Long
Dim BottomRow As Long
Dim LeftColumn As Long
Dim RightColumn As Long
ActualUsedRange TopRow, BottomRow, LeftColumn, RightColumn
Debug.Print TopRow, BottomRow, LeftColumn, RightColumn
End Sub

Rick

Sub ActualUsedRange(StartRow, EndRow, StartColumn, EndColumn)
Dim X As Long
If VarType(StartRow) > 6 Or VarType(EndRow) > 6 Or _
VarType(StartColumn) > 6 Or VarType(EndColumn) > 6 Then
MsgBox "Numeric data types only!", vbCritical, "Data Type Error"
Exit Sub
End If
With ActiveSheet.UsedRange
StartRow = .Row + .Rows.Count - 1
StartColumn = .Column + .Columns.Count - 1
For X = .Row To .Row + .Rows.Count - 1
With ActiveSheet.Cells(X, Columns.Count)
If EndColumn < .End(xlToLeft).Column Then
EndColumn = .End(xlToLeft).Column
End If
End With
With ActiveSheet.Cells(X, 1)
If ActiveSheet.Cells(X, 1).Value <> "" Then
StartColumn = 1
ElseIf StartColumn > .End(xlToRight).Column Then
StartColumn = .End(xlToRight).Column
End If
End With
Next
For X = .Column To .Column + .Columns.Count - 1
With ActiveSheet.Cells(Rows.Count, X)
If EndRow < .End(xlUp).Row Then
EndRow = .End(xlUp).Row
End If
End With
With ActiveSheet.Cells(1, X)
If ActiveSheet.Cells(1, X).Value <> "" Then
StartRow = 1
ElseIf StartRow > .End(xlDown).Row Then
StartRow = .End(xlDown).Row
End If
End With
Next
End With
End Sub