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.
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,797
Messages
5,513,486
Members
408,953
Latest member
Skiig

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top