hello,

could anyone help...pls ???

my problem is,that in one column i have several values but some rows
are empty.

I need to copy this values(with whole column selected ) and paste it to
another column but without empty rows.



is it possible????

Thanks a lot for any answer


--
tommy_gtr
------------------------------------------------------------------------
tommy_gtr's Profile: http://www.excelforum.com/member.php...o&userid=26089
View this thread: http://www.excelforum.com/showthread...hreadid=474106

tommy_gtr,

Sample code...
Sub PasteButOmitBlanks()
Range("B5:B10").Copy
Range("G4").PasteSpecial Paste:=xlPasteAll, SkipBlanks:=True
Application.CutCopyMode = False
End Sub

Jim Cone
San Francisco, USA




"tommy_gtr"
wrote in message

hello,
could anyone help...pls ???
my problem is,that in one column i have several values but some rows
are empty.
I need to copy this values(with whole column selected ) and paste it to
another column but without empty rows.
is it possible????
Thanks a lot for any answer
tommy_gtr

Really empty cells??

Is your data all formulas or all constants?

If yes...
Select your range to copy
edit|goto|special
Select formulas or constants

Then copy those cells and past to the new location.

Alternatively--if you have a mixture of both formulas and constants...

Select your range
edit|copy
paste it to the new location
select that newly pasted range
edit|goto|special
click Blanks
edit|delete...|shift cells up



tommy_gtr wrote:

hello,

could anyone help...pls ???

my problem is,that in one column i have several values but some rows
are empty.

I need to copy this values(with whole column selected ) and paste it to
another column but without empty rows.

is it possible????

Thanks a lot for any answer

--
tommy_gtr
------------------------------------------------------------------------
tommy_gtr's Profile: http://www.excelforum.com/member.php...o&userid=26089
View this thread: http://www.excelforum.com/showthread...hreadid=474106

--

Dave Peterson

hello,

thanks all for your ides.

but i think that "ilyas" is nearest to thing what I need.

dear Ilyas can you help with a liitle modification of your code.

I have problem that range where your code will place my values start
always from first row of column.

and when I tried specify range with something like this:

Range("E12" & cnt).Value = ce.Value

.... it doesnt works


please help me with this.....

tomm

--
tommy_gt
-----------------------------------------------------------------------
tommy_gtr's Profile: http://www.excelforum.com/member.php...fo&userid=2608
View this thread: http://www.excelforum.com/showthread.php?threadid=47410

Hi Dave,

I think a little bit of your solution and its pretty good.

Its easy and its exactly what I need...I didnt see it at first
sight...


so special thanks to you...


tommy


--
tommy_gtr
------------------------------------------------------------------------
tommy_gtr's Profile: http://www.excelforum.com/member.php...o&userid=26089
View this thread: http://www.excelforum.com/showthread...hreadid=474106

tommy_gtr Wrote:
hello,

thanks all for your ides.

but i think that "ilyas" is nearest to thing what I need.

dear Ilyas can you help with a liitle modification of your code.

I have problem that range where your code will place my values start
always from first row of column.

and when I tried specify range with something like this:

Range("E12" & cnt).Value = ce.Value

.... it doesnt works


please help me with this.....

tommy

If you do not want to start from first row then initialize cnt valu
for your row to start.


Code
-------------------
Sub test()

Dim myRng As Range
Dim ce As Range
Dim cnt

Set myRng = Range("D1:D20")
cnt=12 'this is the value from the row number to start
For Each ce In myRng
If ce.Value < 0 Then
Range("E" & cnt).Value = ce.Value
cnt = cnt + 1 'store row increment value
End If
Next ce

End Su
-------------------

--
ilyaskaz
-----------------------------------------------------------------------
ilyaskazi's Profile: http://www.excelforum.com/member.php...fo&userid=2396
View this thread: http://www.excelforum.com/showthread.php?threadid=47410

thank you once again....

but I forget for one thing and it may be a big problem.

cause I want to paste it at same range from which I copy it.....

and its doesnt work.

is there any quick solution???

tommy


--
tommy_gtr
------------------------------------------------------------------------
tommy_gtr's Profile: http://www.excelforum.com/member.php...o&userid=26089
View this thread: http://www.excelforum.com/showthread...hreadid=474106

If you're pasting over the original range, it really sounds like you want to
eliminate the empty cells.

select the range
edit|goto|special
click blanks
edit|delete
(entire row or shift cells up is up to you)

tommy_gtr wrote:

thank you once again....

but I forget for one thing and it may be a big problem.

cause I want to paste it at same range from which I copy it.....

and its doesnt work.

is there any quick solution???

tommy

--
tommy_gtr
------------------------------------------------------------------------
tommy_gtr's Profile: http://www.excelforum.com/member.php...o&userid=26089
View this thread: http://www.excelforum.com/showthread...hreadid=474106

--

Dave Peterson

tommy_gtr Wrote:
hello,

could anyone help...pls ???

my problem is,that in one column i have several values but some rows
are empty.

I need to copy this values(with whole column selected ) and paste it to
another column but without empty rows.



is it possible????

Thanks a lot for any answer

Try this...

Code:
--------------------

Sub test()

Dim myRng As Range
Dim ce As Range
Dim cnt

Set myRng = Range("D1:D20")

For Each ce In myRng
If ce.Value < 0 Then
cnt = cnt + 1
Range("E" & cnt).Value = ce.Value
End If
Next ce

End Sub

--------------------

Cheers!!!
ilyas


--
ilyaskazi
------------------------------------------------------------------------
ilyaskazi's Profile: http://www.excelforum.com/member.php...o&userid=23969
View this thread: http://www.excelforum.com/showthread...hreadid=474106

I think if i write a macro which will select cells with values and then
I copy and past it ,it will be working.

but i have a problem with macro, which will select non empty cells.

i wrote something like this....

Sub test()

Dim myRng As Range
Dim cel As Range

Set myRng = Range("D1:D20")

For Each cel In myRng

If cel.Value < 0 Then

cel.Select

End If

Next cel

End Sub


but it doesnt work very well....

please help....


tommy


--
tommy_gtr
------------------------------------------------------------------------
tommy_gtr's Profile: http://www.excelforum.com/member.php...o&userid=26089
View this thread: http://www.excelforum.com/showthread...hreadid=474106