CODEWITHSUNDEEP
vbaexcel
Diogo Pedro
Diogo Pedro

Reputation: 11

Vba-Excel loop delete last row of a table

I'm trying to learn a bit of VBA. So I'm new to this.

I want a loop that goes from the second sheet until the last one, and then deletes the last line of a table in single one of the sheet's.

Currently I have this code that I search in the web.

 Sub ApagaLoop()
'Apaga todas as linhas das tabelas, e percorre todas as folhas.
    Dim WS_Count As Integer
    Dim I As Integer
    Dim sht As Worksheet
    Dim LastRow As Long


    ' Set WS_Count equal to the number of worksheets in the active
    ' workbook.
    WS_Count = 7

    ' Begin the loop.
    For I = 2 To WS_Count

        ' Insert your code here.
        Sheets(I).Cells(Rows.Count, 1).End(xlUp).Rows().Select
        Rows(ActiveCell.Row).Select
        Selection.Delete Shift:=xlUp

        ' The following line shows how to reference a sheet within
        ' the loop by displaying the worksheet name in a dialog box.
        MsgBox ActiveWorkbook.Worksheets(I).Name

    Next I

End Sub

And I'm getting an error in:

Sheets(I).Cells(Rows.Count, 1).End(xlUp).Row

Can someone tell me what I'm doing Wrong? Thanks a lot

Upvotes: 0

Views: 1069

Answers (2)

user6432984
user6432984

Reputation:

Here is another approach using For Each ws In Worksheets to reference the worksheets and .Find to reference the last row on the worksheet regardless of what column it is in.

Sub ApagaLoop()
    Dim ws As Worksheet
    Dim Target As Range
    For Each ws In Worksheets
        If ws.Index <> 1 Then

            Set Target = ws.Cells.Find(What:="*", After:=ws.Range("A1"), SearchDirection:=xlPrevious)
            If Not Target Is Nothing Then
                If MsgBox("Do you want to delete " & Target.EntireRow.Address(External:=True), vbYesNo, "Delete Last Row") = vbYes Then
                    Target.EntireRow.Delete
                End If
            End If
        End If
    Next
End Sub

Upvotes: 0

user3598756
user3598756

Reputation: 29421

Most probably your ActiveWorkbook has less then 7 worksheets

so just change

WS_Count = 7

to

WS_Count = ActiveWorkbook.Worksheets.Count

furthermore you can avoid Select/Selection and use a full Range reference, as follows:

Option Explicit

Sub ApagaLoop()
    'Apaga todas as linhas das tabelas, e percorre todas as folhas.
    Dim WS_Count As Integer
    Dim I As Integer

    ' Set WS_Count equal to the number of worksheets in the active
    ' workbook.
    WS_Count = ActiveWorkbook.Worksheets.Count

    ' Begin the loop.
    For I = 2 To WS_Count

        ' deletes the last line of current worksheet
        Worksheets(I).Cells(Rows.Count, 1).End(xlUp).EntireRow.Delete Shift:=xlUp

        ' The following line shows how to reference a sheet within
        ' the loop by displaying the worksheet name in a dialog box.
        MsgBox ActiveWorkbook.Worksheets(I).Name

    Next I

End Sub

Upvotes: 4

Related Questions