Solved How to Delete Rows in Excel if Cell in Column 'A' contains 0

YGC

New Member
I have an Excel workbook where is needed to remove all rows wherever appears a 0 value in a certain column before adding it into a collection (see attached picture as an example: When column D contains 0 value, that row must be deleted).
I tried these actions with MS EXCEL VBO 1.Get Cell value - 2. Is zero? - 3.Remove Row but it's very time consuming (about 200.000 rows in the excel sheet)
So i was wondering if there is anyway of doing it with an additional code stage passing as input de column in excel where to chech is values are equal to 0. My programming skills are not very good so i'm having problems into find this solution.

Also, a macro cannot be executed in this case.

Thank you all for your help.
 

Attachments

  • stock.PNG
    110.5 KB · Views: 130

sivagelli

Well-Known Member
As a work around, you can try getting entire data into a collection and filter collection on the field 'Stock'.

Similarly, you also can apply filter on the excel column D and get the filtered data into collection.

Or is it mandatory to delete rows where Stock = 0?
 

VJR

Well-Known Member
Hi YGC,

You can make use of the instructions and code in Post #7 here.

Basically you need to make a duplicate copy of any of the tabs/actions in the Excel VBO and create a new one and make the below changes along with removal of unwanted parameters.

i. The code marked in bold below are already present on the other post. You just need to add the other non-bold text in the Code stage.

If FilteredCount > 0 then

'This means there were rows returned by the filter
sheet.range(varUsedRange).Offset(1, 0).SpecialCells(12).EntireRow.Delete '12:=xlCellTypeVisible

End if

'Remove any filters
sheet.AutoFilterMode = False

Success = True

ii. The other change that you need to make is

replace below line...
sheet.range(varUsedRange).AutoFilter (Field:=1, Criteria1:=1)

with this...
sheet.range(varUsedRange).AutoFilter (Field:=4, Criteria1:=0)
Field 4 implies Column D and Criteria is 0 since you need to filter the value on 0.

iii. Change this line...
FilteredCount = sheet.AutoFilter.Range.Columns("A").SpecialCells(12).Cells.Count - 1 '12 implies for xlCellTypeVisible

to
FilteredCount = sheet.AutoFilter.Range.Columns("D").SpecialCells(12).Cells.Count - 1 '12 implies for xlCellTypeVisible


iv. You can customise this new action as per your needs by passing the column number or name and the value to be filtered so that it can be made re-usable in other processes too.
 

YGC

New Member
As a work around, you can try getting entire data into a collection and filter collection on the field 'Stock'.

Similarly, you also can apply filter on the excel column D and get the filtered data into collection.

Or is it mandatory to delete rows where Stock = 0?

Hi sivagelli, thanks for your quick response the excel sheet contains about 200.000 rows, in consequence i get an SystemOutofMemory error.
to Apply a filter and the passing it to a collection could work too but since for my needs no additional transformation in the excel is needed, only the same excel but withouth the rows when Stock = 0 i think it would be a faster approach.
 

YGC

New Member
Hi YGC,

You can make use of the instructions and code in Post #7 here.

Basically you need to make a duplicate copy of any of the tabs/actions in the Excel VBO and create a new one and make the below changes along with removal of unwanted parameters.

i. The code marked in bold below are already present on the other post. You just need to add the other non-bold text in the Code stage.

If FilteredCount > 0 then

'This means there were rows returned by the filter
sheet.range(varUsedRange).Offset(1, 0).SpecialCells(12).EntireRow.Delete '12:=xlCellTypeVisible

End if

'Remove any filters
sheet.AutoFilterMode = False

Success = True

ii. The other change that you need to make is

replace below line...
sheet.range(varUsedRange).AutoFilter (Field:=1, Criteria1:=1)

with this...
sheet.range(varUsedRange).AutoFilter (Field:=4, Criteria1:=0)
Field 4 implies Column D and Criteria is 0 since you need to filter the value on 0.

iii. Change this line...
FilteredCount = sheet.AutoFilter.Range.Columns("A").SpecialCells(12).Cells.Count - 1 '12 implies for xlCellTypeVisible

to
FilteredCount = sheet.AutoFilter.Range.Columns("D").SpecialCells(12).Cells.Count - 1 '12 implies for xlCellTypeVisible


iv. You can customise this new action as per your needs by passing the column number or name and the value to be filtered so that it can be made re-usable in other processes too.
Thank you very much for the attention, I've been making the necesarry changes that you have mentioned . But i get an error message in the output data item which says : "Autofilter method of Range Class Failed". I tried to pass an specific range such ass (D10:D200000), but still does not work, i'm afraid i'm missing something.
See Attached picture of the code I'm using
 

Attachments

  • 1.PNG
    166 KB · Views: 98
  • 2.PNG
    120.1 KB · Views: 62
  • 3.PNG
    36.4 KB · Views: 46
Thank you very much for the attention, I've been making the necesarry changes that you have mentioned . But i get an error message in the output data item which says : "Autofilter method of Range Class Failed". I tried to pass an specific range such ass (D10:D200000), but still does not work, i'm afraid i'm missing something.
See Attached picture of the code I'm using
Could you copy the code across to VBA editor and then use that to debug? From your error message it could be either of the 2 autofilter lines that caused the error. Did you get it to work using just the UsedRange? Try getting that to work first then make it more generic by allowing the range to be specified as an input.
 

VJR

Well-Known Member
Hi YGC,

I have run the object using the supplied code and it is producing the expected results.
At first check by running without using a custom range on a separate test worksheet.
Also be noted that the code is designed to filter ALL data on the sheet (UsedRange) and skips to delete the header row assuming it starts on the first row of Excel (Offset 1,0).
 

YGC

New Member
Hi YGC,

I have run the object using the supplied code and it is producing the expected results.
At first check by running without using a custom range on a separate test worksheet.
Also be noted that the code is designed to filter ALL data on the sheet (UsedRange) and skips to delete the header row assuming it starts on the first row of Excel (Offset 1,0).
Still getting the same message error "Autofilter method of range class failed" with exactly the same code i posted before. The header is ok, is the first row in the Excel.
Could be the reason the Excel version? Otherwise i don'´t know why it works fine for you.
 

YGC

New Member
Could you copy the code across to VBA editor and then use that to debug? From your error message it could be either of the 2 autofilter lines that caused the error. Did you get it to work using just the UsedRange? Try getting that to work first then make it more generic by allowing the range to be specified as an input.
Hi, with one or two Autofilter lines i get the same error "Autofilter method of range class failed" . Can't figure out the reason...
 

VJR

Well-Known Member
Still getting the same message error "Autofilter method of range class failed" with exactly the same code i posted before. The header is ok, is the first row in the Excel.
Could be the reason the Excel version? Otherwise i don'´t know why it works fine for you.
Thoroughly check with a few things like-
- Is the sheet name correct in the parameters of the action
- Is there a filter already applied on the sheet before running the action (although it is handled in the code but worth a check)
- Is the column value of text data type "0" where you are using it as a number 0.

Take a fresh new sheet with just two columns as shown in post 7 of the other link and run your code against it and verify what happens.
 
  • Like
Reactions: YGC

YGC

New Member
Thoroughly check with a few things like-
- Is the sheet name correct in the parameters of the action
- Is there a filter already applied on the sheet before running the action (although it is handled in the code but worth a check)
- Is the column value of text data type "0" where you are using it as a number 0.

Take a fresh new sheet with just two columns as shown in post 7 of the other link and run your code against it and verify what happens.
Hi, there was a filter already applied on the sheet. Tried it with a new sheet and works perfectly fine.
Thank you very much for your help! It was very useful.
 
  • Like
Reactions: VJR

sandy.dakore

New Member
if tis still not resolved or someone looking about same thing, you can try SQL (Data - OLEDB VBO) to get filtered data. as per my experience its much faster than opening Excel and do the filter and get filtered data into collection.

1639581512227.png1639581512227.png
 
Top