getting size or setting it?
 

Hi!

How do I get the "last" used (max x and y) cell of my sheet?

how do I set it?

BR
Sonnich

getting size or setting it?
 

Hi

One way

Sub Lastcellused()
Dim lRow as Long, lCol as Long
ActiveSheet.UsedRange
With Cells.SpecialCells(xlCellTypeLastCell)
lRow = .row
lCol = .Column
End With
End Sub
--
Regards

Roger Govier


"Sonnich" wrote in message
ups.com...
Hi!

How do I get the "last" used (max x and y) cell of my sheet?

how do I set it?

BR
Sonnich

getting size or setting it?
 

Hi Sonnich,

Try:

'=============
Public Sub LastRowNCol()
Dim SH As Worksheet

Set SH = ActiveWorkbook.Sheets("Sheet1") '<<==== CHANGE

MsgBox "Last populated row = Row # " & LastRow(SH) _
& vbNewLine _
& "Last populated row = Column # " & LastCol(SH)

End Sub

'--------------------

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
'<<=============

For information on resetting the UsedRange, see Debra Dalgleish at:

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


---
Regards,
Norman


"Sonnich" wrote in message
ups.com...
Hi!

How do I get the "last" used (max x and y) cell of my sheet?

how do I set it?

BR
Sonnich