How to extract non-blank cells from a column & copy paste them into another column?

[oceanspace]

I'm trying to get a column of raw data, remove the blank cells, and have them be outputted to another column.


Is there a simple way to do this (whether using VBA or using a formula?) Intuitively, I think there's a way to do this using array formulas but am unclear. Happy to hear any thoughts.


To be clear, there are multiple columns in which this scenario occurs. Therefore, I'd like a formula or VBA code that takes a given column, and outputs into another selected column (not just a simple filter out blanks or sort out blanks solution)


Thanks in advance!


Sample Excel Output - Album on Imgur

 

[oceanspace]

Some additional info:

When I say multiple columns, I am referring to a handful of seperate columns that I would like to extract the values from. Therefore, I imagine there to be 3 separate VBA formulas addressing three different ranges (or three separate Excel formulas addressing three different ranges).


Specific requirements:

On worksheet A, for Column F, starting at row 10 (F10), I want to pull all the non-blank data points and output them into worksheet B, where the output range is Column E, starting at row 69 (E69)

I'm doing this exercise for three specific columns that are part of the same worksheet and want the output to be in one long column on another worksheet (starting at E69).


Thanks so much in advance.

 

[My Aswer Is This]

Well this script will do what you asked for.
Using column "F" on sheet named "A" and Column "E" on sheet named "B"

If you have more columns you want this to work on you will have to provide those details:

Sub Filled_Cells()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Sheets("A").Activate
Lastrow = Cells(Rows.Count, "F").End(xlUp).Row
i = 1
    For Each c In Sheets("A").Range("F10:F" & Lastrow)
    If c.Value <> "" Then Sheets("B").Cells(68 + i, "E").Value = c.Value: i = i + 1
    Next
Application.ScreenUpdating = True
End Sub

 

[oceanspace]

Well this script will do what you asked for.
Using column "F" on sheet named "A" and Column "E" on sheet named "B"

If you have more columns you want this to work on you will have to provide those details:

Sub Filled_Cells()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Sheets("A").Activate
Lastrow = Cells(Rows.Count, "F").End(xlUp).Row
i = 1
    For Each c In Sheets("A").Range("F10:F" & Lastrow)
    If c.Value <> "" Then Sheets("B").Cells(68 + i, "E").Value = c.Value: i = i + 1
    Next
Application.ScreenUpdating = True
End Sub

Wow thank you! This looks very promising.

When I tried to input that formula, I received an error saying I needed to define "c".

Any ideas here?

 

[My Aswer Is This]

Sorry I missed that.
Try this:

Sub Filled_Cells()
'Modified 7-25-17 9:20 AM EDT
Application.ScreenUpdating = False
Dim i As Long
Dim c As Range
Dim Lastrow As Long
Sheets("A").Activate
Lastrow = Cells(Rows.Count, "F").End(xlUp).Row
i = 1
    For Each c In Sheets("A").Range("F10:F" & Lastrow)
    If c.Value <> "" Then Sheets("B").Cells(68 + i, "E").Value = c.Value: i = i + 1
    Next
Application.ScreenUpdating = True
End Sub

 

[oceanspace]

thanks! Can't wait to try this. On my way to office. You will be my hero if this works

 

[My Aswer Is This]

Well I hope it works we try hard here but sometimes communications is the hardest thing.

thanks! Can't wait to try this. On my way to office. You will be my hero if this works

 

[oceanspace]

Well I hope it works we try hard here but sometimes communications is the hardest thing.

It worked! You are the best - thank you. Lifesaver.

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

It worked! You are the best - thank you. Lifesaver.

 

[oceanspace]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

Thanks! You are the best.

Not sure if this is possible, but for the destination column (in sheet "B") would the code be able to make it so that the starting point is the first NON-blank row AFTER, for example, row 10?

Any ideas here?