Print and move cells up

Hi all,

I have a question about shifting cells up in VBA. I have seen alot of posts while searching, with descriptions on how to hide whole rows, or deleting cells but what I need to do is this...

I have a blank column A and then parts listed in column B,C,D and E.

When I click on the Print button (macro that I have created) I want it to HIDE cells in B,C,D,E and shift the cells UP so that no blanks are visible. Then Print out

Can this be done???????

Thanx heaps

Assuming there are various blanks in column A, before printout, you could use:

Dim cell as Range, myrng as Range
set myrng = Range("A1:A" & Range("B65536").End(xlUp).Row)

For Each cell In myrng

    If cell = "" Then cell.EntireRow.Hidden = True
    
Next cell

Then, after printout, you can just unhide all of the blanks:

For Each cell In myrng

    cell.EntireRow.Hidden = False
    
Next cell

Thanx muchly!!! Worked perfectly....

sooooooo....how would I write a VBA code to do the same thing for columns then???

I copied your other code into my macro button, but how do I get it to hide columns that have nothing in them and unhide them after printing??

Thanx in advance and again for the last help

You would just need to change myrng and the two hidden properties.

Before printout:

Dim cell as Range, myrngRow as Range, myrowCol as Range
set myrngRow = Range("A1:A" & Range("B65536").End(xlUp).Row)
set myrngCol = Range("A1:" & Range("IV1").End(xlToLeft).Column & "1")

For Each cell In myrngRow

    If cell = "" Then cell.EntireRow.Hidden = True
    
Next cell

For Each cell In myrngCol

    If cell = "" Then cell.EntireColumn.Hidden = True
    
Next cell

After printout:

For Each cell In myrngRow

    cell.EntireRow.Hidden = False
    
Next cell

For Each cell In myrngCol

    cell.EntireColumn.Hidden = False
    
Next cell

Sorry to bother AGAIN,

but I got an error message that said...Method 'Range' of object_Global' Failed

What does this mean and how do I fix it???

Tar again