Removing blank cells automatically from column when copying to another column

I have a simple table with three columns (B3:D30). The values in the cells in these columns are derived from formulae. I want to copy the resulting data from the populated cells into another three columns (say columns F, G and H) without copying the gaps. So the 27 rows in table B3:D30 will reduce (in this case) to data in cells F4:F7, G4:G7 and H4:H7. The actual columns in the table in my real spreadsheet are actually from B4:B200, and there will be lots of blanks that I want removed. In theory at least I might end up with a table which is just F4:H4, or it could be F4:H200 - either way, I want no gaps.
Any ideas? (I don't really want to use VBA. Is there a formula?)
I attach the sample data.
Thanks.
Sample-151122.xlsx
BlosMusicAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Follow these steps....

1) Select the range in col. B:D.
2) Press Ctrl+G and then click on Special
3) Choose Contstants
4) Right click on any of the selected cell and press Ctrl+C to copy.
5) Click the desired cell in col. F where you want to paste the copied cells and paste special as Values.
BlosMusicAuthor Commented:
Sorry, forgot to mention - I want this to happen automatically without having to actually copy + paste. I need a formula to do it. In fact this worksheet will be hidden, and I just want it to happen without intervention by my data input clerk.
Thanks.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
If you are looking for a formula to achieve this, try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

In F4
=IFERROR(INDEX(B$4:B$30,SMALL(IF(B$4:B$30<>"",ROW(B$4:B$30)-ROW(B$4)+1),ROWS(F$4:F4))),"")

Open in new window

and then copy across and down until you get blank cells.

For detail, refer to the attached.
Sample-151122.xlsx
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

BlosMusicAuthor Commented:
Looks good! I'll check a bit later, but that seems to be exactly what I want. Thanks. I will close the question down when I have finally checked.
aikimarkCommented:
You might also do this with VBA code in the worksheet_change event.
Example:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngSrc As Range
    Dim rngArea As Range
    Dim rngTgt As Range
    Dim wksTgt As Worksheet
    Set rngSrc = Worksheets("sheet1").Range("$B$3:$D$30")
    If Intersect(Target, rngSrc) Is Nothing Then
        Exit Sub
    End If
    Set rngSrc = rngSrc.SpecialCells(xlCellTypeConstants)
    Set wksTgt = Worksheets("sheet2")
    wksTgt.Range("F:H").Delete
    Set rngTgt = wksTgt.UsedRange
    Set rngTgt = wksTgt.Range("F2")
    For Each rngArea In rngSrc.Areas
        rngArea.Copy rngTgt
        Set rngTgt = wksTgt.Cells.SpecialCells(xlCellTypeLastCell).Offset(1, -2)
    Next    
End Sub

Open in new window

BlosMusicAuthor Commented:
Sktneer,
Sorry about the delay, but I've been busy. I tried putting in the equivalent of this formula (your expert Comment of 2015-11-22 at 10:29:19) in my real spreadsheet (at least, it's a near copy of the real thing), but it doesn't seem to quite work.
Can you see why the attached spreadsheet isn't working? I copied your formula, with necessary change of cell numbers, etc., and it doesn't work for the "Hours" column. Any idea why?
Also, I have sent a long time trying to understand the logic of your brilliant formula, but it defies me! Can you give me a clue to how on earth it works? It seems very good, but I am foxed.
Sample-151129.xlsx
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Try it like this.....

=IFERROR(INDEX(H$4:H$201,SMALL(IF(H$4:H$201<>0,ROW(H$4:H$201)-ROW(H$4)+1),ROWS(L$4:L4))),"")

Open in new window

The problem with the earlier formula was it was returning the cells which were not blank but your data range is not empty rather it contains 0's instead of blanks in them. So if you change the IF condition to IF(H$4:H$201<>0, the formula will return the correct result.
To understand how the formula works just Google "Excel Index with Small" and you will have plenty of stuff to read about this combination of excel functions.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aikimarkCommented:
Please test this
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngSrc As Range
    Dim wksSrc As Worksheet
    Dim rngArea As Range
    Dim rngTgt As Range
    Dim wksTgt As Worksheet
    Set wksSrc = Worksheets("Sheet1")
    Set rngSrc = wksSrc.Range(wksSrc.Range("B3"), wksSrc.Range("B3").End(xlDown).Offset(0, 2))
    If Intersect(Target, rngSrc) Is Nothing Then
        Exit Sub
    End If
    Set rngSrc = rngSrc.Columns(3).SpecialCells(xlCellTypeConstants)
    Set wksTgt = Worksheets("sheet1")
    wksTgt.Range("H:J").ClearContents
'    Set rngTgt = wksTgt.UsedRange
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Set rngTgt = wksTgt.Range("H3")
    For Each rngArea In rngSrc.Areas
        wksSrc.Range(rngArea.Offset(0, -2), rngArea).Copy rngTgt
        Set rngTgt = rngTgt.End(xlDown)
        If rngTgt.Row = wksTgt.Rows.Count Then
            Set rngTgt = rngTgt.End(xlUp).Offset(1)
        Else
            Set rngTgt = rngTgt.Offset(1)
        End If
        
    Next
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Open in new window

BlosMusicAuthor Commented:
Perfect. Thanks too to others, but I didn't really want to use VBA.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.