VBA - Looping through a Table (ListObject) and deleting a row
24.1 هزار بار بازدید -
4 سال پیش
-
**Get the Excel file here
**Get the Excel file here
https://chrisjterrell.com/blog/213103...
**Grab the Free VBA Quick Reference Guide
https://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 https://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
https://chrisjterrell.com/blog/213103...
**Grab the Free VBA Quick Reference Guide
https://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 https://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
4 سال پیش
در تاریخ 1399/04/12 منتشر شده
است.
24,183
بـار بازدید شده