Hide rows that have dashes in them

[pe]

I need to make a macro that will hide any rows in a range that includes columns A, B, C and D where B, C AND D have a hyphen in them.

Something like if rows("B:B&quot AND rows("C:C&quot AND rows ("D&quot = "-" then hide that row.

I don't know the vba code to do this. Can anyone please help me. thanks

 

[DaleWatson123321]

Hi pe,

Depending on your situation, your intention to hide rows is perhaps the most suitable option, or perhaps not.

I thought I should at least make you aware of another option --- to extract from your data all those rows that do NOT have a hyphen. This data would be extracted to a SEPARATE sheet where you can then produce separate report(s). By using Excel's "database extraction" capability where you specify selection "criteria", you can selectively extract whatever data you wish to "isolate" and generate reports for.

If this is an option you'd like to consider, or learn more about, I can help.

I have example files I can share, or you could email me your file (replace any sensitive data with fictitious data if necessary), with your stated objectives, and I'll return the file after I've inserted the VBA code to automatically extract the data you require.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[bryanbayfield]

pe, if you want to hide rows in a worksheet, then I'd suggest forgetting about VBA and use the Data > AutoFilter method in the User Interface. If you did something in VBA it's likely that you'd just be replicating the functionality of the AutoFilter anyway, so why bother?

If you wanted to extract rows where columns B,C,D didn't have hyphens hen the AdvancedFilter method will do this for you.

Hope this is of use. SuperBry!

 

[pe]

ok, thank you. I need to add this to a much larger macro and that's why i was looking for code. thanks

 

[bryanbayfield]

Shouldn't be too hard to generate the code you want using Advanced Filter from the user interface once you've pressed the macro "record" button.

It will probably be the most efficient way to handle your data (providing you adapt the code properly), which should be a consideration in a large macro. SuperBry!

 

[DaleWatson123321]

pe,

I see the others have also suggested the use of Advanced Filter.

However, be AWARE... that when you record a macro... this is one of the areas where you are likely to run into one of Excel's BUGS. It tells you that you CANNOT extract data to a SEPARATE sheet. This is FALSE - i.e. you CAN extract data to a separate sheet.

There's a second BUG that tells you it's NOT possible to place your "criteria" on a SEPARATE sheet. Again, NOT true - i.e. it IS possible.

Here's an example of a routine for extracting data. It uses the following range names:
a) "data" - to refer to your database
b) "crit" - to refer to your criteria
c) "out" - to refer to your extraction range. Normally you would assign this name to the top row only - i.e. the one containing the field names.

Sub Extract_Data()
Range("data&quot.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:="crit", _
CopyToRange:=Range("out&quot, _
Unique:=False
End Sub

Hope this helps. If you run into any difficulty, please be sure to ask. There are some "quirks" related to setting up the "criteria", so expect to encounter some difficulties.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca