VBA - Not Last Column or Row

[ajocius]

Group,
I'm using the following code to determine the last row and column i
my spreadsheet. Unfortunately the returned numbers are greater than th
true last row/column. I'm about 100 rows past the last used, 115
instead of 1050 and I'm about 20 columns past the last column. I
there something unseen in my blank columns and if so how do a resolv
this issue.

With Worksheets("Sheet A").UsedRange
LastRowCurrent = .Rows.Count
LastColCurrent = .Columns.Count
End With


Ton

 

[Norman Jones]

Hi Tony,

See Debra Dalglish's notes on resetting the used range at:

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

You could also return the last populated row and column with functions like:

Function LastRow(SH As Worksheet)
On Error Resume Next
LastRow = SH.Cells.Find(What:="*", _
After:=SH.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


Function LastCol(SH As Worksheet)
On Error Resume Next
LastCol = SH.Cells.Find(What:="*", _
After:=SH.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function