Delete filtered rows of a table without deleting the rest of the row in worksheet (VBA)

[pikt11]

Hi everyone,

I have a worksheet that contains both a table of data (from columns A to N) and a few filled-in cells to the right of those columns that I want to remain untouched. I've written some VBA code that will filter the table to show all cells in Column I containing 0, and then delete all those rows from my table.

The issue I'm running into is when I delete these rows, the cells outside the table that I want to keep also get deleted. Is there any way I could isolate the rows that I want to delete up to a certain column (N)? Here's my code I'm using:

Sub delete_test()

'First determine if column contains any instances of 0
If Not Columns(9).Find(0, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False) Is Nothing Then
    ' column I contains at least 1 cell with "0"
     ActiveSheet.Range("A1").AutoFilter Field:=9, Criteria1:="0"

    Range("A2:N1000000").delete Shift:=xlUp


Else
'don't need to delete rows
Exit Sub

 

[gallen]

It is possible if your table is a Listobject. IE that you have converted the cells to a table?

If not, you'd need to.

Then it would be a simple process of looping backwards through the table and deleting any table rows rather than sheet rows.

Are you familiar with adding tables in Excel?

 

[Fluff]

How about

Sub delete_test()

'First determine if column contains any instances of 0
   If Not Columns(9).Find(0, lookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False) Is Nothing Then
   ' column I contains at least 1 cell with "0"
      With ActiveSheet
         .Range("A1").AutoFilter Field:=9, Criteria1:="0"
         .AutoFilter.Range.Offset(1).Delete xlShiftUp
      End With
   End If
Exit Sub

 

[pikt11]

This is my revised code with the table as a ListObject and a backwards loop:

Set datatbl = ActiveSheet.ListObjects("data_table2")
numrows = datatbl.Range.rows.Count


For i = numrows To 1 Step -1
   If datatbl.DataBodyRange(i, 9).Value = 0 Then
    datatbl.ListRows(i).delete
    End If
Next i

But running it through, I'm seeing that the code is identifying every value of cells(i,9) to be null/0 even though most of these cells have a non-zero value.

 

[pikt11]

Update to my previous comment!

The code works, I just didn't realize that the row count used in getting a value of the data body range excludes the header.

Final code:

Set datatbl = wks.ListObjects("data_table")
numrows = datatbl.Range.rows.Count - 1


For i = numrows To 1 Step -1
   If datatbl.DataBodyRange(i, 9).Value = 0 Then
    datatbl.ListRows(i).delete
    End If
Next i

Thanks gallen!

 

[Fluff]

Did you try my idea from post#3 which doesn't need a loop?

 

[pikt11]

fluff, I did try your code but it gave me a 1004 error: "Can't move cells in a filtered range or table".

 

[Fluff]

Ok, I hadn't noticed that you were dealing with a table, rather than normal data.

 

[gallen]

Yep, that's what I had in mind.

For future reference you can do

numrows=datatbl.DataBodyRange.Rows.Count

so you don't have to worry about the header row.