Copying data in column A while ommiting blank cells

[gerryger]

I have data in A1:A24. Some of the cells are blank. I would like to copy only the populated cells in column A into column B. However, the data must start in cell B1 and work downward depending on how many entries there are in column A..

For instance if there are only 5 entries in the A1:A24 range then only B1:B5 will have copied values in them.

 

[VoG]

Try

Sub Cpy()
Range("A1:A24").SpecialCells(xlCellTypeConstants).Copy Destination:=Range("B1")
End Sub

 

[gerryger]

Try

Sub Cpy()
Range("A1:A24").SpecialCells(xlCellTypeConstants).Copy Destination:=Range("B1")
End Sub

Since there are other occurrences of what I am trying to accomplish throughout the sheet I was hoping for a formula instead of VB.

 

[Antonw]

Hi,

01. Select A1:A24
02. Click Filter in Data Group
03. Click the filter Arrow
04. Remove tick from 'blanks"
05. OK
06. Select and copy the filtered data
07. Go to cell B1 and paste (If needed paste values)

Now you may get what you wanted

 

[gerryger]

Hi,

01. Select A1:A24
02. Click Filter in Data Group
03. Click the filter Arrow
04. Remove tick from 'blanks"
05. OK
06. Select and copy the filtered data
07. Go to cell B1 and paste (If needed paste values)

Now you may get what you wanted

Does not quite do what I want. This option will find the blank cell and then eliminate or hide that particular row. This has an effect on column B. I need to keep the row and column structure intact.

Also since the data in my example keeps changing I really want to do this via a formula. Filtering the data manually will become redundant.

 

[gerryger]

Does not quite do what I want. This option will find the blank cell and then eliminate or hide that particular row. This has an effect on column B. I need to keep the row and column structure intact.

Also since the data in my example keeps changing I really want to do this via a formula. Filtering the data manually will become redundant.

Sorry....but after fooling around with the filter option it does do what I want but I would prefer a formula if possible because like I said my sheet does change values a lot.

 

[Matty]

Perhaps like:

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Frank</TD><TD>Frank</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Terry</TD><TD>Terry</TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD></TD><TD>Bob</TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Bob</TD><TD>John</TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD></TD><TD>Mick</TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>John</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>Mick</TD><TD></TD><TD></TD></TR></TBODY></TABLE>

Formula in B1 and copied down is:

=IF(ROWS(B$1:B1)<=C$1,INDEX(A$1:A$10,SMALL(IF(A$1:A$10<>"",ROW(A$1:A$10)-ROW(A$1)+1),ROWS(B$1:B1))),"")

Which needs committing with Ctrl+Shift+Enter.

Formula is C1 is:

=COUNTIF(A1:A10,"*?")

Which is committed with just enter.

Matty

 

[gerryger]

Perhaps like:

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Calibri,Arial; font-size: 11pt;" border="1" cellpadding="0" cellspacing="0"><colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"><td>
</td><td>A</td><td>B</td><td>C</td></tr><tr style="height: 18px;"><td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td><td>Frank</td><td>Frank</td><td style="text-align: right;">5</td></tr><tr style="height: 18px;"><td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td><td>Terry</td><td>Terry</td><td>
</td></tr><tr style="height: 18px;"><td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td><td>
</td><td>Bob</td><td>
</td></tr><tr style="height: 18px;"><td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td><td>Bob</td><td>John</td><td>
</td></tr><tr style="height: 18px;"><td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td><td>
</td><td>Mick</td><td>
</td></tr><tr style="height: 18px;"><td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 18px;"><td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 18px;"><td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">8</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 18px;"><td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">9</td><td>John</td><td>
</td><td>
</td></tr><tr style="height: 18px;"><td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">10</td><td>Mick</td><td>
</td><td>
</td></tr></tbody></table>

Formula in B1 and copied down is:

=IF(ROWS(B$1:B1)<=C$1,INDEX(A$1:A$10,SMALL(IF(A$1:A$10<>"",ROW(A$1:A$10)-ROW(A$1)+1),ROWS(B$1:B1))),"")

Which needs committing with Ctrl+Shift+Enter.

Formula is C1 is:

=COUNTIF(A1:A10,"*?")

Which is committed with just enter.

Matty

Everything works great providing the data is text. I run into problems if the cells are numerical. I'm not sure what "*?" signifies for the code for C1. If that can be changed to accept numerical and text that would be great.

 

[gerryger]

Everything works great providing the data is text. I run into problems if the cells are numerical. I'm not sure what "*?" signifies for the code for C1. If that can be changed to accept numerical and text that would be great.

Nevermind figured it out ...replaced "*?" with ">0" for the numeric cells.