Showing cells that have text in

[jonhall]

Hi,

I have a column with text in some of the boxes. I'd like to produce another column that shows only those boxes with text in, ie: essentially removing all the empty cells.

It would look something like this:

<table border="0" cellpadding="0" cellspacing="0" width="187"><col style="mso-width-source:userset;mso-width-alt:3730;width:77pt" width="102"> <col style="mso-width-source:userset;mso-width-alt:3108;width:64pt" width="85"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;width:77pt" height="20" width="102">Orginal column</td> <td class="xl64" style="border-left:none;width:64pt" width="85">New column</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none" height="20">A</td> <td class="xl64" style="border-top:none;border-left:none">A</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none" height="20"> </td> <td class="xl64" style="border-top:none;border-left:none">B</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none" height="20"> </td> <td class="xl64" style="border-top:none;border-left:none">C</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none" height="20"> </td> <td class="xl64" style="border-top:none;border-left:none">D</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none" height="20">B</td> <td class="xl64" style="border-top:none;border-left:none">E</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none" height="20"> </td> <td class="xl64" style="border-top:none;border-left:none">F</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none" height="20"> </td> <td class="xl64" style="border-top:none;border-left:none">G</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none" height="20"> </td> <td class="xl64" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none" height="20">C</td> <td class="xl64" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none" height="20"> </td> <td class="xl64" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none" height="20">D</td> <td class="xl64" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none" height="20"> </td> <td class="xl64" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none" height="20"> </td> <td class="xl64" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none" height="20"> </td> <td class="xl64" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none" height="20">E</td> <td class="xl64" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none" height="20"> </td> <td class="xl64" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none" height="20"> </td> <td class="xl64" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none" height="20">F</td> <td class="xl64" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none" height="20"> </td> <td class="xl64" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none" height="20"> </td> <td class="xl64" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none" height="20"> </td> <td class="xl64" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;border-top:none" height="20">G</td> <td class="xl64" style="border-top:none;border-left:none"> </td> </tr> </tbody></table>

I've tried using PivotTables, but it doesn't quite disaply how I'd like and doesn't update when new data is entered.

Can anyone help?

 

[Gary McMaster]

How about "Auto Filtering" for non-blanks with copy paste?

Gary

 

[Peter_SSs]

This is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
	A	B
1		A
2	A	B
3		C
4		D
5	B	E
6	C
7
8	D
9
10	E
11
List without blanks

 

[Sektor]

Sub F()
    Range("A1").AutoFilter Field:=1, Criteria1:="="
    Range("A2:A" & Cells(Rows.Count, "A").Row).SpecialCells(xlCellTypeVisible).Copy
    Range("A1").AutoFilter
    Range("B1").PasteSpecial
End Sub

 

[Peter_SSs]

Sub F()
    Range("A1").AutoFilter Field:=1, Criteria1:="="
    Range("A2:A" & Cells(Rows.Count, "A").Row).SpecialCells(xlCellTypeVisible).Copy
    Range("A1").AutoFilter
    Range("B1").PasteSpecial
End Sub

If there are blanks in column A, the AutoFilter will not filter to the bottom of the data. In any case the code errors for me.


For a macro approach I would use:

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> NoBlankList()<br>    <SPAN style="color:#00007F">With</SPAN> Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row + 1)<br>        .Value = .Offset(, -1).Value<br>        .SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[jonhall]

Hi Peter,

I've tried your answer and it works on a blank sheet with letters on but not where I want it to. The text (letters A-F in this case) appear from a formula, not typed in. Don't know if that makes a difference.

 

[jonhall]

Thanks for your answers too Gary and Sektor - i'll try them if I can't get this way to work as they seem a bit more complicated and I'm not great at this.

 

[Peter_SSs]

Hi Peter,

I've tried your answer and it works on a blank sheet with letters on but not where I want it to. The text (letters A-F in this case) appear from a formula, not typed in. Don't know if that makes a difference.

Always a good idea to detail in what way something doesn't work. Perhaps tell us what your formula is, what my formula produced incorrectly, post a screen shot with sample data/formulas etc.

If the problem was just that mine produced some error values at the bottom then try this one instead.

=IF(ROWS(B$1:B1)>COUNTIF(A$1:A$100,">"""),"",INDEX(A$1:A$100,SMALL(IF(A$1:A$100<>"",ROW(A$1:A$100)),ROWS(B$1:B1))))

Still confirmed with Ctrl+Shift+Enter.

 

[jonhall]

Hi,

I can't quite remember what error it showed with the first suggestion, but the second one works perfectly.

Thanks very much for you help - it's appreciated.

And thanks again Gary and Sektor.

Jon