Copying text only if the cell is not empty

[Big Chris]

Please can somebody help?

I want to run a macro which starts in cell a1 and if it is not blank to
copy that cells value into another cell and print the sheet. I want
then for the macro to move down to the next non-blank cell and do the
same until it's visited all non-blank cells in the range a1:a50.

Thanks in anticipation!!

 

[Jerry]

-----Original Message-----
Please can somebody help?

I want to run a macro which starts in cell a1 and if it is not blank to
copy that cells value into another cell and print the sheet. I want
then for the macro to move down to the next non-blank cell and do the
same until it's visited all non-blank cells in the range a1:a50.

Thanks in anticipation!!



------------------------------------------------
[/url]
~~ View and post usenet messages directly from http://www.ExcelForum.com/

.

I didn't put the printing command in, but here is
everything else.

Sub CopyNonBlank()
Range("a2").Select
For counter = 1 To 10
If ActiveCell <> "" Then
i = 0
Selection.Copy
ActiveCell.Offset(0, 2).Select
Do Until ActiveCell.Offset(-1, 0) <> ""
ActiveCell.Offset(-1, 0).Select
i = i + 1
Loop
ActiveSheet.Paste
ActiveCell.Offset(i, -2).Select
End If
ActiveCell.Offset(1, 0).Select
Next counter
End Sub

 

[Ron de Bruin]

Hi Big Chris

I am sure if I understand you??
You can loop through all the cells with a value like this

For Each cell In Range("a1:a50").SpecialCells(xlCellTypeConstants)

Please tell what you really want

 

[Big Chris]

I have a range set from a1 to a50 and want to collect data from each non
blank cell in that range, in turn, and paste it into another cell.

Thansk for your help...is that a bit clearer?