VBA - Looping through a Table (ListObject) and deleting a row

**Get the Excel file here
**Grab the Free VBA Quick Reference Guide

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

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

[querrythis]

Beginner learner here. My code was crude. This is so efficient and logical. Thank you so much for this valuable help.

[joaogrilo]

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!

[jacobcrelia]

Bruh! I about lost it when you showed you could turn off screen updates...thanks a billion!!!

[theworddoner]

Thank you so much for making this tutorial. I was banging my head over listobjects until I saw this.

[m-squaredcontractors]

Great tutorial, thank you.Short and straight to the point. Well done!

[simplejuan]

Most of my data in Excel are in tables or listobjects. This tutorial helps a lot. Many thanks. Subscribed!!!

[SRous-yd]

Excellent, I've learned a lot here Thanks.

[ElectromecanicaIndustrial]

very helpful, was struggling with the lookat:=xlWhole for looping through the table but databodyrange solved my issue

[johnabram]

What is the datatype of variable "rw" here? Is it a Range or Long? or ListRow?

[kelitonandre]

Hi. How could I loop through the tables in the database and fill in the name of these tables in an excel spreadsheet?

[YTXYZ]

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?

[anshuljain]

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
("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.

[pauljosephson]

you didn't define/Dim "rw"