VBA - Looping through a Table (ListObject) and deleting a row
**Get the Excel file here
chrisjterrell.com/blog/213103...
**Grab the Free VBA Quick Reference Guide
www.chrisjterrell.com/excel-v...
Looping through a Table (ListObject) in Excel is slightly different than a sheet using "Cells" and arrays using the index in the parenthesis array(x,y). If you are using Tables in Excel, it is a brilliant move. Linking pivot tables to the table is fantastic because you don't have to resize your Pivot if your data grows or shrinks.
However, if you have coded Pivot you may have found them cumbersome. A great resource for coding ListObjects at this link here. In this link, "TheSpreadsheetGuru" gives you more than enough info to learn how to code ListObject
The Key to looping through an Object is to know its name or index. Which is similar to how you would code a Sheet and Cell. The second thing you need to know is the HeaderRowRange and the DatabodyRange because that is how you will loop through the ListObject
CODE
'A great blog post on www.thespreadsheetguru.com/bl...
Sub listobjLoop()
Application.ScreenUpdating = False
Dim lo As ListObject
Set lo = Sheet1.ListObjects("Table1")
For cl = 1 To lo.HeaderRowRange.Count
If lo.HeaderRowRange(cl) = "weight" Then Exit For
Next
num = 2500
For rw = lo.DataBodyRange.Rows.Count To 1 Step -1
If lo.DataBodyRange(rw, cl) LT num Then
lo.ListRows(rw).Delete
End If
Next
Application.ScreenUpdating = True
End Sub
Пікірлер: 33
Beginner learner here. My code was crude. This is so efficient and logical. Thank you so much for this valuable help.
Thank you so much for making this tutorial. I was banging my head over listobjects until I saw this.
@EverydayVBAExcelTraining
2 жыл бұрын
Great to hear. Glad it helped
Thanks for your well explained and relevant video. At first, I couldn't understand why you decided to loop through the bottom to the top of the Table, so I decided to do the opposite, which I immediately regret of... When you delete a row, the table rows move one row to the top, so going from the bottom to the top ensures that every row that meets the criteria will be deleted. Thanks a lot!
Bruh! I about lost it when you showed you could turn off screen updates...thanks a billion!!!
@EverydayVBAExcelTraining
2 жыл бұрын
I think I lost it too when I learned that!
Great tutorial, thank you.Short and straight to the point. Well done!
@EverydayVBAExcelTraining
3 жыл бұрын
Thanks for the Comment
Most of my data in Excel are in tables or listobjects. This tutorial helps a lot. Many thanks. Subscribed!!!
@EverydayVBAExcelTraining
3 жыл бұрын
Glad it helped!
Excellent, I've learned a lot here Thanks.
Thank you, very inspiring video
@EverydayVBAExcelTraining
3 жыл бұрын
Glad you liked it
Excellent video.
Thanks a million. Great video.
@EverydayVBAExcelTraining
3 жыл бұрын
Thank you!
That was very help. Thumbs it is.
Great tutorial 👍👍
@EverydayVBAExcelTraining
4 жыл бұрын
Thank you I appreciate it
very helpful, was struggling with the lookat:=xlWhole for looping through the table but databodyrange solved my issue
What is the datatype of variable "rw" here? Is it a Range or Long? or ListRow?
@MultiBodmin
Жыл бұрын
I would imagine it to be an integer and it represents the current row controlled by the "For rw = lo.DataBodyRange.Rows.Count To 1 Step -1" for loop
Hi. How could I loop through the tables in the database and fill in the name of these tables in an excel spreadsheet?
@EverydayVBAExcelTraining
3 жыл бұрын
Do you know how to make a database connection? If will contact to the database and use SQL to get all the database tables.
I really liked your video, but you really need slow down a little to give the viewer time to follow where you are clicking and getting information (time 4:23 to 4:35). This may be very apparent to power users, but fortunately I could pause and replay. Also where did you DIM the "rw" variable in your code? In some other sub or is that not required to for this single loop?
Hi I need your help I am making excel VBA code in the Developer tab to create Powerpoint Presentation I have successfully generated PowerPoint slides using my VBA code in excel But, I want to set the background as an image. I am unable to do so In my excel VBA code, I have the variable ppSlide, that is for the newly created Slide In my excel VBA code, I am running the code ppSlide.Background.Fill.UserPicture ("Address of the picture that is local to my Laptop hard drive with JPG extension") Entire code is getting executed, but the background image of my Slide is not getting in the Slide I am using MS Office 2007 Note : I have already added Microsoft Powerpoint 12.0 Object Library in my Tools -> References Kindly help me.
@EverydayVBAExcelTraining
4 жыл бұрын
Could the issue be because of a PowerPoint theme
@EverydayVBAExcelTraining
4 жыл бұрын
You may have to move the image to the front or make sure nothing is in front of it
@anshuljain7925
4 жыл бұрын
@@EverydayVBAExcelTraining I'll try to work on your suggestions and get back to you. Thanks for the reply
you didn't define/Dim "rw"
@EverydayVBAExcelTraining
7 ай бұрын
Yeah, I have constantly gone back an forth on that one. To not define variables is NOT best practice but it also takes time to explain on videos and can be more confusing for someone new to vba. I don't know what the right answer is here.