VBA - Looping through a Table (ListObject) and deleting a row
HTML-код
- Опубликовано: 1 июл 2020
- **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
Beginner learner here. My code was crude. This is so efficient and logical. Thank you so much for this valuable help.
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!
Thank you so much for making this tutorial. I was banging my head over listobjects until I saw this.
Great to hear. Glad it helped
Bruh! I about lost it when you showed you could turn off screen updates...thanks a billion!!!
I think I lost it too when I learned that!
Great tutorial, thank you.Short and straight to the point. Well done!
Thanks for the Comment
Most of my data in Excel are in tables or listobjects. This tutorial helps a lot. Many thanks. Subscribed!!!
Glad it helped!
Excellent video.
Excellent, I've learned a lot here Thanks.
Great tutorial 👍👍
Thank you I appreciate it
Thanks a million. Great video.
Thank you!
Thank you, very inspiring video
Glad you liked it
That was very help.
Thumbs it is.
very helpful, was struggling with the lookat:=xlWhole for looping through the table but databodyrange solved my issue
Hi. How could I loop through the tables in the database and fill in the name of these tables in an excel spreadsheet?
Do you know how to make a database connection?
If will contact to the database and use SQL to get all the database tables.
What is the datatype of variable "rw" here? Is it a Range or Long? or ListRow?
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
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.
Could the issue be because of a PowerPoint theme
You may have to move the image to the front or make sure nothing is in front of it
@@EverydayVBAExcelTraining
I'll try to work on your suggestions and get back to you.
Thanks for the reply
you didn't define/Dim "rw"
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.