vba to find the last row in a table in excel

[dpaton05]

I have a table that can have rows added to it by pressing a button and I need to know the vba code to determine the last row in the table. The button is pressed when the table is finished and no more lines need adding. Could someone help me as I am still learning how to code?

Thanks,
Dave

 

[My Aswer Is This]

Assuming the name of the Table is Table1
Then try this:

Sub Table_Lastrow()
'Modified  11/1/2018  6:53:23 PM  EDT
Dim ams As Long
ans = ActiveSheet.ListObjects("Table1").DataBodyRange.Rows.Count
MsgBox "Last row in Table is  " & ans
End Sub

 

[dpaton05]

That code tells me how many lines the table has, I need code that tells me the row reference of the last row in the table, such as row 21.

 

[Trebor76]

The code is correct as it shows the number of records in a table. To reconcile it the actual row number in the sheet you'd need to add the header row like so:

Option Explicit
Sub Macro1()

    Dim lngLastRow As Long
    
    With ActiveSheet.ListObjects("Table1")
        lngLastRow = .DataBodyRange.Rows.Count + .HeaderRowRange.Row
    End With

End Sub

You could just use the following code to find the last row on a tab across columns A to E (change to suit) regardless of whether there's a table on the tab or not (note though it will error out if there's no data on the tab):

lngLastRow = Range("A:E").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Regards,

Robert

 

[My Aswer Is This]

I assume what your still trying to do I remember your post from a few days ago.
Is put a image two rows below your last row in a table.

But since your not providing all the details.

If your image is in the active sheet with the table and the image is named Picture 2

Try this:

Sub Copy_Shape()
'Modified  11/1/2018  7:57:06 PM  EDT
Dim ans As Long
ans = ActiveSheet.ListObjects("Table1").DataBodyRange.Rows.Count
With ActiveSheet.Shapes("Picture 2")
.Left = ActiveSheet.ListObjects("Table1").DataBodyRange.Cells(ans + 3, 1).Left
.Top = ActiveSheet.ListObjects("Table1").DataBodyRange.Cells(ans + 3, 1).Top
End With
End Sub

 

[My Aswer Is This]

Now if your wanting to copy the shape to two cells below the last line of the table
This is why it's always important to provide specific details.
I'm guessing the Picture is named Picture 2


Try this:

Sub Copy_Shape()
'Modified  11/1/2018  8:16:06 PM  EDT
Dim ans As Long
ans = ActiveSheet.ListObjects("Table1").DataBodyRange.Rows.Count
ActiveSheet.Shapes("Picture 2").Copy
ActiveSheet.ListObjects("Table1").DataBodyRange.Cells(ans + 3, 1).PasteSpecial
End Sub

 

[dpaton05]

I have decided that this is a bad way to do this as their might have to be notes added under the table and I don't know how many notes are not set so I don't know where the bottom will be. I have now decided that I am going make 3 buttons that will insert the signature on the bottom of pages 1, 2 or 3. Trouble is, if the button is clicked more than once, it will cascade the signatures and you can end up with a heap of unnecessary signatures.

I have a button that deletes the signatures which works if there is only 1 signature but if the button has been clicked multiple times or the buttons to put the signature at the bottom of different pages have all been clicked, there will be signature files with different name, such as image1, image2 and so forth, depending on how many times it was clicked. Do you know the code to delete all images in a set range, delete all active x images or create a loop that will see if there is an image1, and if there isn't the loop will just end. If there is an image1, it will delete it and then check if there is an image2. If there is an image2, it will delete it and then check if there is an image3 and so forth until it can't find an image.

That is only just an idea I had so I am open to other methods of deleting these unnecessary images.

Thanks again for helping me.

 

[Trebor76]

I have decided that this is a bad way to do this as their might have to be notes added under the table and I don't know how many notes are not set so I don't know where the bottom will be.

As long as the notes are not comments within in the cell (right-click > Inset Comment) but simply text in cells, my second piece of code will find the last row

Looks like you're already getting help with your other MrExcel thread here regarding deleting ActiveX controls.