Delete rows based on a cell value in the row

[Guest]

I'd like to delete rows from a spreadsheet based on the value of a cell in a
row. This is a very large spreadsheet (60,000+ lines). I want to delete
rows based on a value (in this case all parts which are purchased- vs mfd)
without destroying the spreadsheet structure. In this case I have several
bills of material where I want to list all the assemblies, but not the
purchased parts. It seems it should be possible, but I'm not having any luck
with the filter method.

Thanks,

Dave

 

[Don Guillett]

sort??
or use a macro to delete, from the bottom up, if the cell contains your text

 

[Guest]

I don't want to sort, because I'd lose the BOM structure. I just want to
remove the purchased items from the lists so I have the manufactured part
numbers left in the BOM structure.

How would you create a macro to do that?

 

[Beege]

Dave,

Have you tried using the autofilter for the purchased field?

Beege

 

[Guest]

Beege,

Yeah, I tried the autofilter, but maybe (probably) I'm not familiar enough
with it. When I filter, how can I specify I just want to see the mfd parts
without destroying the BOM structure?

 

[Don Guillett]

You CAN sort by creating a column with numbers>sort to delete desired>resort
based on numbers

for i=cells(rows.count,1).end(xlup).row to 2 step -1
if cells(i,"c")="yourtext" then rows(i).delete
next i

 

[Beege]

Dave
Is there a separate column for the make/buy indicator? You'd autofilter on
that one. Select the column, Data/Filter Autofilter. Or you could try
messing around with advanced filter, but I don't use that much

Beege

 

[Guest]

Thanks. I'll try it.

Dave

Dave
Is there a separate column for the make/buy indicator? You'd autofilter on
that one. Select the column, Data/Filter Autofilter. Or you could try
messing around with advanced filter, but I don't use that much

Beege