Select last 30 rows of a specific table in a sheet with multiple tables

xefa

New Member
Joined
Sep 7, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I'm trying to select a range containing last 30 rows in a specific table so i could then paste it where needed.

I only found solutions on how to select the same for last x filled rows in a sheet. However my sheet contains 3 different tables (all same amount of rows but different amount of columns) from which i need to copy last 30 rows every time i run the macro.

Any advice is apreciated
Kind regards
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,689
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

Is this what you mean?
I have assumed the table has at least 30 rows of data.

VBA Code:
Sub Last30()
  With Range("Table2")  '<- Edit to your table name
    .Offset(.Rows.Count - 30).Resize(30).Select
  End With
End Sub

Note too that you rarely need to select ranges in vba to work with them and selecting slows your code. As an example, this would copy the range to a range starting at Z1 without having to select it.
VBA Code:
Sub Last30_v2()
  With Range("Table2")  '<- Edit to your table name
    .Offset(.Rows.Count - 30).Resize(30).Copy Destination:=Range("Z1")
  End With
End Sub
 
Solution

xefa

New Member
Joined
Sep 7, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Thank you Peter, this helps a lot, especially the v2 part as im always happy to improve at this.

I'm still learning and mostly only capable of gluing existing code together.

I tested the 2nd solution and it works well, however I'm having trouble pasting that data. Is the method different when using code that copies the content without selecting them?

Specifically i tried to PasteSpecial with transpose in other sheet but end up with PasteSpecial error which i assume is because it actually has no copied data?


VBA Code:
Worksheets("Test").Range("A1").PasteSpecial Transpose:=True

Sorry for extending the original question like this.
Kind regards
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,689
Office Version
  1. 365
Platform
  1. Windows
Try this

VBA Code:
Sub Last30_v3()
  With Range("Table2")  '<- Edit to your table name
    .Offset(.Rows.Count - 30).Resize(30).Copy
    Worksheets("Test").Range("A1").PasteSpecial Transpose:=True
  End With
End Sub
 

xefa

New Member
Joined
Sep 7, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Thank you Peter for your help this works !

I was putting it outside the With as i tough it only affects Table

Have a splendid day
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,689
Office Version
  1. 365
Platform
  1. Windows
I was putting it outside the With
That should be fine.
This works just as well for me.

VBA Code:
Sub Last30_v4()
  With Range("Table2")  '<- Edit to your table name
    .Offset(.Rows.Count - 30).Resize(30).Copy
  End With
  Worksheets("Test").Range("A1").PasteSpecial Transpose:=True
End Sub

I assumed that the problem earlier was that you not removed this pert of my original code
Rich (BB code):
.Offset(.Rows.Count - 30).Resize(30).Copy Destination:=Range("Z1")
 

Forum statistics

Threads
1,147,451
Messages
5,741,202
Members
423,648
Latest member
steel1968

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top