VBA Question & General Advice

[JimmyC]

Excel XP & Win Xp sp3

I have inherited an excel file that is basically an invoice data file from a commerical billing software application. There are about 60,000 rows of data. In column D are cells with Invoice=XXXXXX {the x's are numeric numbers} but there is also other data in cells in column D.

I have been asked to hide from printing the invoice=XXXXXX cells. My first thought was to use VBA to change the cell color of every cell with Invoice=XXXXXX to white so it would not be visible on the hard copy. I am stuck trying to make this work as I can't figure out how to use logic to "read" all the cells in column B and only change the font color when the cell contains Invoice=XXXXXX. The macro recorder, which is my best VBA friend, does not support "logic" code...

But while I was struggling how to do this in VBA...I wondered if the work sheet was printed to a PDF using pdf printer---I know the end user would not "see" the Invoice=XXXXXX, but would this data still be included in the PDF--so that if someone changed font colors in the PDF---the Invoice=XXXXXX information would again be viewed?

If this is possible, then I guess the macro should delete the invoice=XXXXXX and not change the font color. The reason I wanted to change the font color was that we wanted to retain the invoice numbers but not release this data element to the person receiving this report. If the VBA macro deletes the Invoice=XXXXXX, I would keep 2 copies of the EXCEL file, one with the invoice numbers and one without.

Thanks for your advice and help with the VBA code....
JimC

[HansV]

Do you only want to hide (or remove) the cells with Invoice=.... themselves, or the entire row?

[JimmyC]

Hans,
Thanks for the reply...I cannot hide or remove the entire row as there required data elements in the other columns. While certain cells in Column B have other data, I would be looking to "clear" the cells in column B, that have Invoice=XXXXXX. The trigger would almost have to be something like "starts with: "Invoice=" to ignore the changing numeric values in the invoice number. Does this make sense? Thanks
JimC

[HansV]

You can easily do this without a macro:
- Save the workbook if necessary.
- Select column D.
- Select Edit | Replace... (Excel 2003 or before) or click Find & Select | Replace... in the Home tab of the ribbon (Excel 2007 or later).
- Enter Invoice=* in the Find what box.
- Leave the Replace with box blank.
- Click Replace All.
- Print the workbook, or convert to PDF, or whatever.
- Close it without saving it.

[JimmyC]

Hans,
Thanks so much...I learned something new today...I have never left the replace box blank---I guess I "assumed" that it had to be filled or this functionality was lost. Thanks again.
JimC