• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find and delete the last row in a table.

A

ankuun

New Member
Hi everyone,

I am looking for a macro that will find and delete the last (blank) row in a table (It can look for a value in any table cell).

It gets tricky because I have two tables in one sheet and I need two individual macros for each table. I don't want macro to find any blank row, instead i want it to delete the very last row. When I write a macro it also deletes the very first row so i need this macro to stop at row 1 (not the header) but It can clear the content in row 1.

I need this macro to be table1 specific. I can probably figure out the macro for the second table.

Any help would be appreciated.
Thanks!
 
Hi ,

A clear problem specification will help.

Let us assume you have a table , whose range address is C5:K77.

Row 5 is the column headers row , while C6 through C77 are the row headers.

The data itself is in the range D6 through D77.

Now , can you clarify / confirm the following ?

1. Do you wish to delete row 77 , or row 77 only if it is entirely blank ?
The definition of the row being entirely blank is if all the cells in the range D77:K77 are blank.

2. Do you wish to delete any blank rows in the range Row 7 through Row 77 ?
You have mentioned that you do not want to delete Row 6 , even if it is entirely blank.

3. Do you wish to clear the contents of Row 6 ?

In case your problem specification differs from the above , please indicate the specific areas where it differs.

Narayan
 
Hi Narayank991,

Thank you for your interest in my question and you are right; I should've been more specific.

This is going to be the user form and it will include the header and row 6 then, if the user feels the need to add rows, they will be able to do it using this macro.

1. I want it to delete row 77 if the range D77:K77 are blank.
I was thinking maybe if the deletion is based on the cell value C6:C77 (using if statement), I could throw in a msgbox asking "are you sure you want to delete the row".

2. I don't want it to delete any blank rows, only the last one. Yes I want row 6 to be available all the time.

3. It would be great if it clears content when the macro comes to row 6.

4. Finally, I want it to be table specific because I am going to have 2 tables in one sheet.

This is what I worked on today and it works for the first table although it does delete the row 6.

Code: 
Sub DeleteLastRow()
 
Dim oLst As ListObject
 
Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:=""
If ActiveSheet.ListObjects.Count > 0 Then
    For Each oLst In ActiveSheet.ListObjects
        If oLst.ListRows.Count > 0 Then
          oLst.ListRows(oLst.ListRows.Count).Delete
        End If
    Next oLst
End If
ActiveSheet.Protect Password:=""
End Sub
 
Hi ,

Can you try this ?
Code: 
Public Sub Delete_Last_Row_If_Blank()
            Dim listobj As ListObject
            Dim lastrow As Long
            For Each listobj In ActiveSheet.ListObjects
                With listobj
                    If .Name = "Table1" Then
                        number_of_columns = .ListColumns.Count
                       lastrow = .ListRows.Count
                       If Application.WorksheetFunction.CountBlank(.ListRows(lastrow).Range.Offset(, 1).Resize(, number_of_columns - 1)) = number_of_columns - 1 Then ' The check does not include the first column of the table
                          .ListRows(lastrow).Delete
                        End If
                    End If
               End With
           Next
End Sub
Narayan
 
Narayank991,

Thank you so much for the code. I changed it little bit and works great for my table. Only thing that is missing is that I still can't get it to ClearContents for row 6.

Here is the code if anyone needs it.

Code: 
Public Sub DeleteLastRow()
 
 
'This one works and its table specific
 
Dim oLst As ListObject
 
Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:=""
 
If ActiveSheet.ListObjects.Count > 1 Then
    For Each oLst In ActiveSheet.ListObjects
        With oLst
            If .Name = "Table1" Then
                If oLst.ListRows.Count > 1 Then
                number_of_columns = .ListColumns.Count
            oLst.ListRows(oLst.ListRows.Count).Delete
        End If
      End If
    End With
    Next
ActiveSheet.Protect Password:=""
End If
End Sub
 
Hi ,

Sorry , I forgot about that !

Change your procedure to include the following statement :

oLst.ListRows(1).Range.Clearcontents

Code: 
Dim oLst As ListObject
 
Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:=""
 
If ActiveSheet.ListObjects.Count > 1 Then
    For Each oLst In ActiveSheet.ListObjects
        With oLst
            If .Name = "Table1" Then
                If .ListRows.Count > 1 Then
                number_of_columns = .ListColumns.Count
                .ListRows(.ListRows.Count).Delete             
                .ListRows(1).Range.Clearcontents
        End If
      End If
    End With
    Next
ActiveSheet.Protect Password:=""
End If
End Sub

Narayan
 
Narayan,

I have been trying this code and I am still having difficulty with clearing row 6.
The code you posted clears row6 at the same time with row 7. I mean, when you try to delete row 7, it automatically clears row 6 too.
I think I need a private macro where this macro (above) will run when the conditions are met.

Thanks a lot for all the help!
 
Hi ,

I am not clear on what your requirement is ; can you explicitly mention all your requirements ?

1. If your table has only one row of data which is blank

2. If your table has only one row of data which is not blank

3. If your table has two rows of data , where the last row is blank in one case , and non-blank in a second case

4. If your table has more than two rows of data , where the last row is blank in one case , and non-blank in a second case

Please clearly explain the expected output in each case.

Narayan
 
Narayank,

I really appreciate your help and interest.
This is a very silly (but annoying) problem and I will try to explain all the requirements.

The whole idea was making a form without using Infopath. I wanted copy InfoPath's Repeating table option to excel.
Row 6 will always be there to start with. It will be blank (actually it will have formulas).
Users are going to add rows to this table (Lets Say 4 rows)
Then if they want to delete, they can delete these rows one at a time, starting at the end
When the deleting process comes back to row 6, it will only clear the content for row 6.

Form at the beginning​
Row 6: (blank)​
​
entering 4 rows of data.​
Row6: "Example data"​
Row7: "Example data"​
Row8: "Example data"​
Row9: "Example data"​
​
When you change your mind and start deleting, macro will start deleting from row 9 and go up until it hits Row6. It will stop on Row 6 (not deleting but clearing row6).​
​
Form at the end​
Row 6: (blank)​

1. If my table has only one row of data (row 6), do nothing because there is no data.
2. If my table has only one row of data (row 6) which is not blank, clear content when macro is run.
3. If my table has two rows of data
Case 1: Delete the blank row (7)​
Case 2: Delete the non-blank row (7)​
4. If my table has more than tow rows of data;
Case 1: Delete the blank row.​
Case 2: Delete the non-blank row.​
​
 
Hi ,

Thanks for the details , everything is clear now.

However , I am going to be out for a few hours today , and I'll be able to post the code only when I'm back ; can you check back later today ?

Narayan
 
Hi ,

Can you see if this is OK ?
Code: 
Public Sub DeleteLastRow()
           Const DELETE_ONLY_IF_BLANK = True               '  This flag if set to FALSE will ensure a non-blank row is also deleted
           Const CHECK_ONE_COLUMN_LESS = True              '  This flag if set to TRUE will delete only if all columns are blank
           
           Dim oLst As ListObject
           Dim Number_of_rows As Long
           Dim Number_of_columns As Integer
           
           Application.ScreenUpdating = False
           ActiveSheet.Unprotect Password:=""
 
           For Each oLst In ActiveSheet.ListObjects
               With oLst
                    If .Name = "Table3" Then               '  Change the table name to suit
                       Number_of_columns = .ListColumns.Count
                       Number_of_rows = .ListRows.Count
                       If Number_of_rows > 1 Then
                          If DELETE_ONLY_IF_BLANK Then
                             If Application.WorksheetFunction.CountBlank(.ListRows(Number_of_rows).Range.Offset(, 1).Resize(, Number_of_columns + 1 * CHECK_ONE_COLUMN_LESS)) = Number_of_columns + 1 * CHECK_ONE_COLUMN_LESS Then
                                .ListRows(Number_of_rows).Delete
                             End If
                          Else
                             .ListRows(Number_of_rows).Delete
                          End If
                       End If
                       .ListRows(1).Range.ClearContents
                    End If
               End With
           Next
           ActiveSheet.Protect Password:=""
           Application.ScreenUpdating = True
End Sub
Narayan
 
Narayan,

Your code looks like it would work but for some reason it only clears the firs row without deleting any other rows.
When I read your code, looks like it got everything and there is no reason why it wouldn't work.
I think I need to spend some time on modifying your code.

Thank you so much for your time!
 
I see!
That changes everything, I can't believe I missed constants part.
However, I still can't get "ClearContents" part work separately. Whenever I run the macro, it clears the first row when there are several more rows.
 
Hi ,

I am again confused by the following statement :

it clears the first row when there are several more rows

Do you mean that clearing of the first row should happen only when there are 2 rows of data ? If so , just wrap the following statement within an IF statement as shown below :

If Number_of_rows = 2 then .ListRows(1).Range.ClearContents

Narayan
 
This works great!
You got it to work exactly how I wanted it.

Thank you very much Narayan. You are really good at this!
I really hope you will be interested in my future questions/posts :)
I greatly appreciate your help!
 
You must log in or register to reply here.
Back
Top