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:
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:
Code:
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