I want to add text to certain rows of my worksheet which are visible only at
Print Preview/Print modes only.

I want these details to hide or preferablly to store in a seperate sheet
during edit to save screen space.

e.g : I want to display only the "Product part number" during edit. But to
included "indepth Product details" when I send it to Print/PrintPrview.

How to handle this?

you might want to use Workbook_BeforePrint in ThisWorkbook module

Private Sub Workbook_BeforePrint(Cancel As Boolean)
For Each cell In Selection
cell.Value = "indepth Product details: " & cell.Value
Next cell
End Sub

forgot to mention to select only those cells that have to be appended
with "indepth Product details"