Macro to clear range contents 2

[MSBrady]

Oy!
Excel 2000
Windows 2000

I'm stuck. I'm not a VBA programmer and am having trouble trying to piece together code snipets to do what I need done.

Background: I have a workbook that acts as a report for doctors. On the "Pre-Publish" worksheet I have links to other worksheets that contain Pivots. The pivot tables are not able to take on the look and feel my CEO needs for the report. So I get the data from SQL Server 2000 to the pivots page and then link to it in a way that is asthetically pleasing on the report page.

The issue: There is a portion of the report that contains the practices top 40 CPT codes by charges. It also shows how many of those codes they performed, any monies posted against those CPTs and some other calculations. Now b/c it is imperitive that the report be able to be opened and then printed w/o any user interaction I map the 40 rows of data ahead of time. However at the beginning of the month there aren't enough procedures done to fill all 40 rows with data, so I end up with rows that have no meaning. To make things worse there are calculations done based on the 40 rows, like Sums, Diffs and so on. So a typical report may look something like:
A B C D E
Description CPT Billed Procedures Postings

History 99499 $900 4 $350
PostOp 99024 $30 3 $30
#VALUE! - $- #NAME? $-
#VALUE! - $- #NAME? $-
#VALUE! - $- #NAME? $-
#VALUE! - $- #NAME? $-
#VALUE! - $- #NAME? $-
#VALUE! - $- #NAME? $-

The first two rows are legit, the rest aren't. I need a macro that fires on open which looks at the value in column A and if it is not a CPT (i.e. #VALUE!) then remove the range for that row. Now the clincher is that after the report has been run for a given day I need a closing macro that undoes what the opening macro did, so that the next time I open it there will be current data and then the open macro fires and the dance goes on.

A B C D E
Description CPT Billed Procedures Postings

History 99499 $900 4 $350
PostOp 99024 $30 3 $30
Admit 45698 $50 6 $400
Surgery 32165 $4000 2 $1000
#VALUE! - $- #NAME? $-
#VALUE! - $- #NAME? $-
#VALUE! - $- #NAME? $-
#VALUE! - $- #NAME? $-

I hope this is not too confussing.

TIAFYH

Mike

 

[RonRepp]

Hi Mike:

Having lived in the world of CPT and DRG codes, I pity you. Anyway, to auto open a procedure you go to the VBE and double click on ThisWorkbook.

On the left, use the dropdown and choose Workbook. On the right dropdown, use either open or BeforeClose. Something like this.
============================
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.Close Savechanges:=False
End Sub

Private Sub Workbook_Open()
RunSomeProcedure
End Sub
===========================

From there, since you don't want to lose your spreadsheet, I would recommend hiding the rows.

I hope this helps.

Ron


Ron Repp

 

[MSBrady]

Ron:

Thanks for the quick reply. Hiding them isn't possible due the printing setup, there has to be a certain number of rows or it throws the whole thing off when I export to .PDF. I thought about just coloring them white, however there are calculations based on the rows, so no matter what they need to be cleared. The clearing is the part that I am struggling with.

Any more ideas?

Thanks,
Mike

 

[PHV]

Why not playing with the ISERROR worksheet function in your formulas ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MSBrady]

A step in the right direction.
Thanks! I put a ISERROR flag at the end of the row and told the macro that if it's False then clear row contents. I still need to experiment with clearing just a range of the row, rather than the whole row.

 

[ries]

I think PH advises you to use the ISERROR function per formula so per cell. Something like =if(iserror(formula),"",formula). In other words if the formula returns an error, fill the cell with "".

Hope this helps,

ries

 

[MSBrady]

Oh Shizzle Snap! You're right! You know you guys rock my face off. Thanks