Extracting cells with text from one range and copying them to another range on a different sheet while removing blank cells

[divineld]

I'm trying to extract cells with text from one range and copy them to another range on a different sheet. For example, I have text in some cells that range from G12:G25. Many of the cells are blank, so I'm looking for something that will take the cells that do have text and copy them to another range (E5:E14) on another worksheet while removing the blank cells. Any tips would be greatly appreciated. Thank you.

 

[kevin9999]

Could you provide a sample of your data using the XL2BB add in, or alternatively share your file via Google Drive, Dropbox or similar file sharing platform? There's a number of different ways you could do this, but difficult to demonstrate without actual data.

 

[kevin9999]

Here's some options for you to consider - 1 x formula or 2 x VBA (put in standard modules).

Book1
	G
11
12	text
13	text
14
15	text
16	text
17	text
18
19	text
20	text
21	text
22
23	text
24	text
25
26
Sheet1

Book1
	E
5	text
6	text
7	text
8	text
9	text
10	text
11	text
12	text
13	text
14	text
15
Sheet2

Cell Formulas
Range	Formula
E5:E14	E5	=FILTER(Sheet1!G12:G25,Sheet1!G12:G25<>"")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Sheet1!_FilterDatabase	=Sheet1!$G$12:$G$25	E5

Option Explicit
Sub Test_Copy_1()
    'Change the sheet names to suit
    With Worksheets("Sheet1").Range("G12:G25")
        .AutoFilter 1, "<>"
        .Copy Worksheets("Sheet2").Range("E5")
        .AutoFilter
    End With
End Sub

Sub Test_Copy_2()
    Dim c As Range, r As Range
    'Change the sheet names to suit
    For Each c In Worksheets("Sheet1").Range("G12:G25").SpecialCells(xlCellTypeConstants)
        If r Is Nothing Then Set r = c Else Set r = Union(r, c)
    Next c
    r.Copy Worksheets("Sheet2").Range("E5")
End Sub

 

[divineld]

So, I could share a screenshot at the moment. I would like to take the range illustrated below with the red box (G12:G44) and pull the names into a smaller range onto the "Sheet 1" tab to consolidate.

And as an example, copy only the names into the following range located on the "Sheet 1" tab.

If you need the actual spreadsheet, then what functions would you recommend for accomplishing this?

 

[kevin9999]

I think our posts may have crossed in the aether
Post #3 adjusted for your sheets:

Book1
	C
4	text
5	text
6	text
7	text
8	text
9	text
10	text
11	text
12	text
13	text
14	text
15	text
16	text
17	text
18	text
19	text
20	text
21	text
22	text
23	text
24	text
25	text
26	text
27	text
28
Sheet1

Cell Formulas
Range	Formula
C4:C27	C4	=FILTER('Sample Roster'!G12:G44,'Sample Roster'!G12:G44<>"")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
'Sample Roster'!_FilterDatabase	='Sample Roster'!$G$12:$G$25	C4

Option Explicit
Sub Test_Copy_1()
    With Worksheets("Sample Roster").Range("G12:G44")
        .AutoFilter 1, "<>"
        .Copy Worksheets("Sheet1").Range("C4")
        .AutoFilter
    End With
End Sub

Sub Test_Copy_2()
    Dim c As Range, r As Range
    For Each c In Worksheets("Sample Roster").Range("G12:G44").SpecialCells(xlCellTypeConstants)
        If r Is Nothing Then Set r = c Else Set r = Union(r, c)
    Next c
    r.Copy Worksheets("Sheet1").Range("C4")
End Sub

 

[divineld]

Worked well. Thanks!

 

[kevin9999]

Glad to help & welcome to the Forum!
Just out of interest, which option did you go with?

 

[divineld]

Thanks. I went with the non-vba filter option.