asked on

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

Subodh Tiwari (Neeraj)

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.

BlosMusic

ASKER

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)

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

BlosMusic

ASKER

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.

aikimark

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

BlosMusic

ASKER

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

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aikimark

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

BlosMusic

ASKER

Perfect. Thanks too to others, but I didn't really want to use VBA.