0

Trying to delete the last two rows in a macro that formats the sheets in a specific way. Everything else works fine except the last two rows of the data do not get deleted

My entire code is:

Sub Format()

'Firstly convert all text cells into number cells for every sheet in workbook

Application.ScreenUpdating = False

For Each ws In Sheets
    On Error Resume Next
    For Each r In ws.UsedRange.SpecialCells(xlCellTypeConstants)
        If IsNumeric(r) Then r.Value = (r.Value) * 1
        ''Or test the values in the next column on the right
        'If IsNumeric(r) Then r.Offset(0,1).Value = (r.Value)*1
    Next r

'Remove excess columns and rows
    Dim lastRow As Long
'Find last row in column A
    lastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
'Remove Last 2 rows with data
    ws.Rows(lastRow - 1 & ":" & lastRow).Delete
'Delete rows and columns
    ws.Rows("1:15").Delete
    ws.Columns("A").Delete

Next ws
Application.ScreenUpdating = True

End Sub

this is the code I'm trying to use to delete the last two rows in each sheet:

    Dim lastRow As Long
'Find last row in column A
    lastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
'Remove Last 2 rows with data
    ws.Rows(lastRow - 1 & ":" & lastRow).Delete
Zacchini
  • 143
  • 13
  • The last row number will change from sheet to sheet – Zacchini Jan 15 '20 at 05:51
  • All columns are of same size? mean if A45 (last row) has data then rest of the columns will have data till row 45 only? If not then use `.Find` to find the last row. – Siddharth Rout Jan 15 '20 at 05:53
  • Few other things.. **1.** Do not use `On Error Resume Next` as a blanket statement. It will hide other erros as well. **2.** Instead of `If IsNumeric(r) Then r.Value = (r.Value) * 1` you may want to copy `1` and pastespecial with operation as `multiply` to achive the same result. BTW I am not sure what are you trying to achieve by doing this? Converting numbers stored as text to number? if yes, then there are better ways to do it. – Siddharth Rout Jan 15 '20 at 05:57
  • Where do I put `.Find`? – Zacchini Jan 15 '20 at 06:02
  • and yes that part of the code converts numbers stored as text to number. I just got it from another post – Zacchini Jan 15 '20 at 06:03
  • [.Find to find last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920) – Siddharth Rout Jan 15 '20 at 06:06
  • http://www.informit.com/articles/article.aspx?p=2027553&seqNum=11 – Siddharth Rout Jan 15 '20 at 06:08
  • tried using `.Find` but get a Compile error: Method or data member not found at `(.Cells)` – Zacchini Jan 15 '20 at 06:15
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/205983/discussion-between-siddharth-rout-and-zacchini). – Siddharth Rout Jan 15 '20 at 06:16

0 Answers0