Copying data in column A while ommiting blank cells

gerryger

Board Regular
Joined
Apr 19, 2010
Messages
54
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.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

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

gerryger

Board Regular
Joined
Apr 19, 2010
Messages
54
Try

Code:
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

New Member
Joined
Feb 17, 2010
Messages
42
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

Board Regular
Joined
Apr 19, 2010
Messages
54

ADVERTISEMENT

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

Board Regular
Joined
Apr 19, 2010
Messages
54
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

Well-known Member
Joined
Feb 17, 2007
Messages
3,710

ADVERTISEMENT

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:

Code:
=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:

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

Which is committed with just enter.

Matty
 

gerryger

Board Regular
Joined
Apr 19, 2010
Messages
54
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:

Code:
=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:

Code:
=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

Board Regular
Joined
Apr 19, 2010
Messages
54
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,615
Messages
5,832,713
Members
430,156
Latest member
Amosjack1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top