help with displaying information

[woknick]

Here is my problem: I have a column that is 33 cells deep that ha
scattered numbers in that column. For example

1

5

9
12
13

15


17

where the spaces are blank cells. I want to grab those numbers an
place them is an output that looks like
1,5,9,12,13,15,17

So I just want to pull those numbers from those cells and display the
together.

thanks in advanc

 

[Myrna Larson]

Not sure what you want to do with the data, but I've put it in the next column
to the right, starting at row 2.

Sub Test()
Dim NonBlanks() As Variant
N = 0
C = 1
ReDim NonBlanks(1 To 33, 1 To 1)
For R = 1 To 33
X = Cells(R, C).Value
If X <> "" Then
N = N + 1
NonBlanks(N, 1) = X
End If
Next R
Cells(2, C + 1).Resize(N, 1).Value = NonBlanks()
End Sub

 

[William]

Hi woknick

Sub test()
'Assuming all cells are constants
Range("A1:A33").SpecialCells(xlCellTypeConstants, 23).Copy Range("B1")
'If you then want to delete Column A...
Columns("A:A").Delete Shift:=xlToLeft
End Sub

--
XL2002
Regards

William

(e-mail address removed)

| Here is my problem: I have a column that is 33 cells deep that has
| scattered numbers in that column. For example
|
| 1
|
| 5
|
| 9
| 12
| 13
|
| 15
|
|
| 17
|
| where the spaces are blank cells. I want to grab those numbers and
| place them is an output that looks like
| 1,5,9,12,13,15,17
|
| So I just want to pull those numbers from those cells and display them
| together.
|
| thanks in advance
|
|
| ---
| Message posted
|

 

[William]

As an alternative...

Sub test()
Columns("A:A").Copy Range("B1")
Columns("B:B").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
End Sub


--
XL2002
Regards

William

(e-mail address removed)

| Hi woknick
|
| Sub test()
| 'Assuming all cells are constants
| Range("A1:A33").SpecialCells(xlCellTypeConstants, 23).Copy Range("B1")
| 'If you then want to delete Column A...
| Columns("A:A").Delete Shift:=xlToLeft
| End Sub
|
| --
| XL2002
| Regards
|
| William
|
| (e-mail address removed)
|
| | | Here is my problem: I have a column that is 33 cells deep that has
| | scattered numbers in that column. For example
| |
| | 1
| |
| | 5
| |
| | 9
| | 12
| | 13
| |
| | 15
| |
| |
| | 17
| |
| | where the spaces are blank cells. I want to grab those numbers and
| | place them is an output that looks like
| | 1,5,9,12,13,15,17
| |
| | So I just want to pull those numbers from those cells and display them
| | together.
| |
| | thanks in advance
| |
| |
| | ---
| | Message posted
| |
|
|
|

 

[woknick]

thanks Myrna Larson, it works grea