finding last row

[lior03]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Range("A2:C880")
.Sort Key1:=Range("C1"), key2:=Range("B1")
.RemoveSubtotal
.ClearOutline
End With
End Sub
hello

the following macro sort a table up to line 880 which at the moment is the last cell.
how can i get the last row automaticlly?
thanks

[Bob Phillips]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Range("A2:C" & Cells(Rows.Count, "A").End(xlUp).Row)
.Sort Key1:=Range("C1"), key2:=Range("B1")
.RemoveSubtotal
.ClearOutline
End With
End Sub

[matthewspatrick]

For an excellent treatment of the subject:

http://www.vbaexpress.com/forum/articles.php?action=viewarticle&artid=53

[lior03]

hello
is it possible to tell excel to include a row in print area altough it is not connected to other rows?.
for example.in the following macro - how do i tell excel to include the last row at the buttom, the one that count all rows in the printed area?

Application.ScreenUpdating = False
ActiveSheet.DisplayRightToLeft = False
With ActiveSheet.PageSetup
.PrintArea = ("A2:D" & Cells(Rows.Count, "A").End(xlUp).row)
.PrintGridlines = True
.PrintTitleRows = ("$A$1:$C$1")
.CenterFooter = "&P of &N pages"
.CenterHeader = "&A"
.RightFooter = "&T"
.RightHeader = "&F"
.LeftHeader = "&D"
End With
Dim lngRow As Long
Dim lngCom As Long
lngRow = ActiveSheet.Range("a1").End(xlDown).row
lngCom = ActiveSheet.Range("a1").End(xlToRight).Column
With Cells(lngRow + 2, lngCom)
.FormulaR1C1 = "=countA(R2C:R[-1]C)"
.Font.ColorIndex = 3
End With
With Cells(lngRow + 2, lngCom - 1)
.Value = "total items on list :"
End With
Cells.HorizontalAlignment = xlRight
Application.ScreenUpdating = True


thanks

[mdmackillop]

For an excellent treatment of the subject:

http://www.vbaexpress.com/forum/articles.php?action=viewarticle&artid=53

While the articles are inaccessible, have a look here.
http://xlvba.3.forumer.com/index.php?showtopic=23

[mdmackillop]

Set the print area after you've created the last entries

Application.ScreenUpdating = False
ActiveSheet.DisplayRightToLeft = False
Dim lngRow As Long
Dim lngCom As Long
lngRow = ActiveSheet.Range("a1").End(xlDown).Row
lngCom = ActiveSheet.Range("a1").End(xlToRight).Column
With Cells(lngRow + 2, lngCom)
.FormulaR1C1 = "=countA(R2C:R[-1]C)"
.Font.ColorIndex = 3
End With
With Cells(lngRow + 2, lngCom - 1)
.Value = "total items on list :"
End With
With ActiveSheet.PageSetup
.PrintArea = "$A$2:" & Cells(lngRow + 2, lngCom).Address
.PrintGridlines = True
.PrintTitleRows = ("$A$1:$C$1")
.CenterFooter = "&P of &N pages"
.CenterHeader = "&A"
.RightFooter = "&T"
.RightHeader = "&F"
.LeftHeader = "&D"
End With
Cells.HorizontalAlignment = xlRight
Application.ScreenUpdating = True