VBA - Not Last Column or Row
 

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

--
ajociu
-----------------------------------------------------------------------
ajocius's Profile: http://www.excelforum.com/member.php...fo&userid=1769
View this thread: http://www.excelforum.com/showthread.php?threadid=47365

VBA - Not Last Column or Row
 

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

---
Regards,
Norman



"ajocius" wrote in
message ...

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

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


Tony


--
ajocius
------------------------------------------------------------------------
ajocius's Profile:
http://www.excelforum.com/member.php...o&userid=17695
View this thread: http://www.excelforum.com/showthread...hreadid=473652