I need to find the last Cell in Column C with a value in it and delete all Rows below and then Total column C.
I would also like to set used range to print.
Find last used row, delete un-used rows and total
-
JohnDrew -
February 19, 2017 at 4:59 PM -
Thread is marked as Resolved.
-
-
-
Re: Find last used row, delete un-used rows and total
Code
Display MoreOption Explicit Sub delAllAfterLastRow() Dim WS As Worksheet Dim LastCell As Range Dim LastCellRowNumber As Long Set WS = Worksheets("Sheet1") With WS Set LastCell = .Cells(.Rows.Count, "C").End(xlUp) LastCellRowNumber = LastCell.Row Rows(LastCellRowNumber + 1 & ":" & Rows.Count).Delete End With End Sub
-
Re: Find last used row, delete un-used rows and total
After looking closer at your project, here is an updated version that I believe will work better for you:
Code
Display MoreOption Explicit Sub delAllAfterLastRow() On Error Resume Next Sheets("Sheet1").Range("C:C").SpecialCells(xlCellTypeConstants).ClearContents On Error GoTo 0 End Sub
[TABLE="width: 506"]
[tr]
[/tr][tr][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr][tr]
[TD="colspan: 7"]Formula in B2, copied down : =IFERROR(VLOOKUP(A2,Sheet2!$A$2:$C$20,2,0),"")[/TD]
[/tr][tr][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr][tr]
[TD="colspan: 7"]Formula in C2,copied down : =IFERROR(VLOOKUP(A2,Sheet2!$A$2:$J$20,3,0),"")[/TD]
[td][/td][td][/td][td][/td][td][/td][/tr][tr][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr][tr]
[TD="colspan: 3"]Formula in D2 : =SUM(C:C)[/TD]
[td][/td][td][/td][td][/td][td][/td][/tr]
[TD="colspan: 3"]Inventory Table on Sheet 2[/TD]
[/TABLE] -
Re: Find last used row, delete un-used rows and total
Thanks
-
Re: Find last used row, delete un-used rows and total
Your welcome.
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!