• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

Eliminating blank spaces

I'm sure this question has a very simple answer that I should know, but don't.  In the cells of column A are either a number or blank.  The numbers appear every so often, with the blank cells in between.  In column B I want to just show the numbers.  Example:

Column A        Column B
                            4
                            2
     4                     3
                            5
     2
     

     3

     5

How do I generate column B?
0
pwflexner
Asked:
pwflexner
1 Solution
 
Martin LissOlder than dirtCommented:
In a macro.

Dim lngLastRow as Long
Dim lngIndex As Long
Dim lngRow As Long
lngLastRow = Range("A65536").End(xlUp).Row

lngRow = 2
For lngIndex = 2 To lngLastRow
    If Cells(lngIndex, 1).Value <> "" Then
        Cells(lngRow, 2).Value = Cells(lngIndex, 1).Value
        lngRow = lngRow + 1
    End If
Next

Open in new window

0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

there are several ways.

Copy and paste the data, then sort the pasted values. Or use a formula

If you don't want to sort, you can use a helper column and a formula. Insert a column after column A and starting in row 2 (assuming row 1 has labels)

=IF(A2<>"",ROW(),"")

copy down

Then use in C2

=INDEX(A:A,SMALL(B:B,ROW(A1)))

copy down. Copy the result up to the error message and paste as values.

cheers, teylyn
0
 
SteveCommented:
you could:

Add a filter to the top row.
Filter on Blanks in column A.
Highlight all rows
Press [ctrl]+[numpad minus]

This will delete all rows without values in the filtered column.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
pwflexnerAuthor Commented:
Actually the blank spaces in column A have a "" in them, if this matters...
0
 
Martin LissOlder than dirtCommented:
Well then in my code just substitute that character (which I can't make out) in this line

If Cells(lngIndex, 1).Value <> ""
0
 
pwflexnerAuthor Commented:
Thanks to all, but this answer worked best for my needs.
0
 
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2012
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now