Squeeze out the empty cells for printing?

[mikejan]

Hey everyone. See lots of chatter about hiding rows and columns, but I need to figure out how to squeeze out individ cells that came back with a blank from an IF formula (see image below).

Want to eliminate all the red space so I have a neat list per day. Any ideas? Thanks

 

[MARK858]

Want to eliminate all the red space so I have a neat list per day. Any ideas?

I'm mightedly confused what you want here

If you delete the individual cells then what do you want to replace them as it shifts from either right,left,up or down? or if you clear the cells then the formulas go but you still have the cells appearing blank or as you stated you can hide the columns or/and rows.

Perhaps you can provide a sample of how you want it to look afterwards?

 

[mikejan]

Thanks for the note, sorry about the confusion. Want to take the data from this:

TO THIS ===================================================================

Was thinking there might be was a way to pull (reference) the data out of the one spreadsheet that does the calculations - and then display within a 2nd sheet to print.

Any ideas?

 

[MARK858]

Try the code below but change Sheet1 to the name of your sheet. Code goes in a regular module.

Sub DeleteMe()
    Dim test As Worksheet, x As Long, lr As Long
    Sheets("[COLOR="#FF0000"]Sheet1[/COLOR]").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "aSHEET"
    lr = Sheets("aSHEET").Range("R:R").Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
    For x = lr To 3 Step -1
        If Sheets("aSHEET").Cells(x, "R") = "" Then Sheets("aSHEET").Cells(x, "R").Delete Shift:=xlUp
    Next
    Sheets("aSHEET").PrintOut
    Application.DisplayAlerts = False
    Sheets("aSHEET").Delete
    Application.DisplayAlerts = True
End Sub

 

[mikejan]

GREAT! Thanks so much Will play with it when I get a chance.

 

[MARK858]

Your welcome but just remember I have taken a chance on what you have in those so called blank cells as you haven't stated or shown the formula (I can tell you have a formula because of the error triangles which in itself is a bit concerning).

I am assuming it is the same formula as is in R14? copied/dragged down which is giving an empty string i.e. "" (note: an empty string isn't a blank cell, they are 2 different things).

For future reference when posting on the board have a look at my signature block for some ways of posting usable screenshots, as in general a lot of posters won't answer questions with images that can't be copied into Excel as they don't want to waste their time re-typing data to test on (they just move on to another thread )