Deleting Rows In Excel (using Macro) that Meet a Certain Criteria

[jakatz]

I know this is probably really simple for the VB(A) afficiando's out there, so here goes.

I have an Excel inventory file I put together - I am now looking to automate it a bit. One of the things I am looking to do in a VB script is to take the main spreadsheet, copy it, sort by the final inventory count, and in the column which identifies any variances (which I am sorting the end result of the inventory by), delete all rows that in the "variance" column is either blank or "0" (either case indicates there is nothing further to be done with that part). The end result would be to provide strictly a list of the part numbers which need to be adjusted in our inventory system.

Anyone have any good ideas out there? Any help is appreciated.

 

[SkipVought]

Hi,

Why not just use the AutoFilter? Why do you want to delete rows? Just filter them out.

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[jakatz]

Skip -

Thought about that, but I have users who will view the sheet and say "oh what are those nice arrows doing on top of the header row . . ." and potentially end up messing up the filter and have to call me to help them understand what they did (and end up doing it again for them).

I have to keep the original in tact (thus the reason for copying the spreadsheet) - and then I eliminiate a bunch of unecessary columns, sort by the end result variance and delete the rows I don't need.

Besides, I thought it might be something interesting I can do to begin messing with VBA without it being terribly complex. I know Excel very well, but am just starting to understand VBA, and would like to get more out of it.

 

[AnotherHiggins]

jakatz,

Being familiar with Excel, you already know there are several ways to go about deleting the rows you want deleted. A good way to get your feet wet with VBA is to simply turn on the VB Recorder, go through the steps to delete the rows with null/zero values in the variance column, then observe the code that was produced.

Doing things this way, you will run into situations where things don't work quite the way you expected, but fixing those problems is a great way to learn!

Give it a shot and post back with any code questions you may have.


PS This is no big deal, but just for future reference VBA questions should probably be posted to forum707, VBA Visual Basic for Applications (Microsoft) Forum.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[KenWright]

Given that you want to keep the source data but provide an Inventory report on a filterd basis, have you considered using a Pivot table to do just that? All depends on your data and what kind of report you want to build, but on the face of it I'm guessing it would meet most of the criteria you have outlined.

Also, that way you possibly have no need to go down the code route.

Regards
Ken........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[MNSVB]

jakatz - I agree with John, I once wrote an Excel Macro that contained many VBA commands that ended up being close to 20 pages long when printed. I created a vast majority of it by recording the manual steps (creating/deleting a page, deleting rows...) and then editing the macro and inserting common VBA code like If/Then, Do while and so on.

MNSVB