Conditional statement to delete rows based on values in two columns

Question

I've tried searching here and generally through Google to find a solution to suit my needs, but have come up empty. I'm new to using Excel VBA, but I assume this is the correct way to go about things in this case.

I have a large spreadsheet - many worksheets with thousands of rows in each. Each worksheet has similar data in it. There are 5 columns, A-E. Column C has duplicate data, and column E disambiguates it. What I'm looking for is a way to delete rows when the condition of "If c28=c29=c30=c31, and e28:e31 contains '.tif', then delete rows where e28:e31 is not '.tif'."

I hope this makes sense.

Thanks for any help!

I see you are new to VBA in Excel, is there anything that you've tried so far? — Soulfire, Sep 16 '15 at 15:18
lol "come up empty"?? http://stackoverflow.com/questions/11562093/delete-rows-based-on-condition-in-a-column?s=6|2.6246 http://stackoverflow.com/questions/14613609/how-to-delete-row-based-on-cell-value?s=4|2.8762 http://stackoverflow.com/questions/11317172/delete-row-based-on-condition?s=7|2.6021 http://stackoverflow.com/questions/30055943/how-to-delete-rows-based-on-criteria-from-2-columns-in-vba?s=13|2.3984 http://stackoverflow.com/questions/28349141/delete-rows-based-on-criteria-in-multiple-columns?s=14|2.3781 — findwindow, Sep 16 '15 at 15:22
@findwindow -- Did you really laugh out loud? I'm just trying to imagine you sitting at your computer cackling like a hyena over this... — rory.ap, Sep 16 '15 at 15:27
@M.Peachy happy to help if it looks like questioner has tried to help them selves. I just added your question title into Google and it returns 3.7 million results... — whytheq, Sep 16 '15 at 15:38

score 0 · Answer 1 · answered Sep 17 '15 at 11:10

I do not understand why people waste so much time searching for exactly the code they need for a problem that is unique to them. It would be so much easier to learn the basics of VBA and then code their solution themselves

Search for “Excel VBA Tutorial”. There are many to choose from so pick one that matches your learning style. I prefer books. I visited a good library; borrowed the most promising Excel VBA Primers to study at home then bought the one I preferred as a permanent reference book. A few hours learning VBA and you could solve this problem and the next and the next without wasting hours wandering around the web looking at code you do not understand.

Think about your requirement. I assume c28=c29=c30=c31 is an example. c31=c32=c33=c34 would also be of interest. You need to generalise your specification.

I will replace c28 by Cells(28, "C") which happens to be VBA syntax but any suitable notation will do at this stage. I think you mean:

Cells(RowCurrent, "C") = Cells(RowCurrent+1, "C") And _
Cells(RowCurrent, "C") = Cells(RowCurrent+2, "C") And _
Cells(RowCurrent, "C") = Cells(RowCurrent+3, "C")

for all permitted values for RowCurrent.

What are the permitted values for RowCurrent? If you have one header row, the first data row will be 2 so the minimum value for RowCurrent will be 2. If RowLast is the last used row of the worksheet, the maximum value for RowCurrent is RowLast – 3.

When you say e28:e31 do you means cells e28, c29, c30 and e31 have been merged?

Do you mean e28:e31contains .tif or e28:e31ends .tif? Would you keep abc.tif.txt? When programming, your specification must be absolutely precise. The computer will do what you say not what you meant.

You say you have many worksheets and you want to read each of them in turn. I would expect any tutorial to explain how to loop through all the worksheets of a workbook. Alternatively, this is the sort of question you can search for. If you have a single requirement, you can usually find a suitable match. Put another way, searching for D may be easy but searching for A and D and I is difficult.

You have an outer loop for each worksheet. You need to find the last used row of each worksheet. You have an inner loop for each block of rows. You need to delete uninteresting blocks. I would expect you to learn how to do each of those from your tutorial. Can you put those four separate techniques together?

I can only see one serious complication with your requirement; you do not delete uninteresting rows, you keep interesting rows. If you examine rows 2 to 5 and find they do not conform to your template, you cannot delete them because rows 3 to 6 might conform to your template. I do not believe you could possibly find any code that would meet this requirement. You would have to design the solution yourself. I have a couple of suggestions but I would need to have more confidence in your exact requirement before suggesting them.

Conditional statement to delete rows based on values in two columns

1 Answers1