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

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

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

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

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

yes that's what I need...eliminate blank cells.

but it is not so easy, cause I have a 12 columns and in each column ar
some filled rows and some blank rows. so i can't easy delete some row
cause it can caused that I delete also row which is not blank in othe
column.

and with solution I have another problem....sometimes it works an
sometime it doesn't....

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

I don't understand the problem. When you do it manually, you'll see an option
to shift the cells up or delete the entire rows. Just choose to shift the cells
up.

In fact, if you really just want to eliminate the empty cells in a range, you
can select that range (not just a single column) and do that technique:

For instance, I could start with:

$A$1 $B$1 $C$1 $D$1
$A$2 $C$2 $D$2
$A$3 $B$3
$B$4

$B$6
$B$7

$B$9 $C$9 $D$9
$A$10 $C$10
$A$11 $C$11 $D$11
$A$12 $D$12
$A$13 $C$13 $D$13


And finish with:

$A$1 $B$1 $C$1 $D$1
$A$2 $B$3 $C$2 $D$2
$A$3 $B$4 $C$9 $D$9
$A$10 $B$6 $C$10 $D$11
$A$11 $B$7 $C$11 $D$12
$A$12 $B$9 $C$13 $D$13
$A$13


All the gaps have disappeared.


tommy_gtr wrote:

yes that's what I need...eliminate blank cells.

but it is not so easy, cause I have a 12 columns and in each column are
some filled rows and some blank rows. so i can't easy delete some rows
cause it can caused that I delete also row which is not blank in other
column.

and with solution I have another problem....sometimes it works and
sometime it doesn't....

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

dear Dave

I tried to write macro for shift the cells up ....
it looks like this:

sub test ()
Range("B13:D13").Select

Compare_1:
If ActiveCell.Value = "" Then
Selection.delete Shift:=xlUp
End If

If ActiveCell.Value = "xxxx" Then
GoTo Konec_1
End If

If ActiveCell.Value < "" Then
ActiveCell.Offset(1, 0).Range("A1:C1").Select
End If

GoTo Compare_1

Konec_1:
end sub


but this is not very sophisticated solution and when I have a table
with 250 rows...it's not very fast...

so now I'm trying to find solution which will find cells < "" and then
select the range.

it looks like this

Sub test2()

Range("K13:M13").Select

Hledej:
If ActiveCell.Value = "" Then
ActiveCell.Offset(1, 0).Range("A1:C1").Select
End If

If ActiveCell.Value < "" Then
ActiveCell.Offset(0, 0).Range("A1:C252").Select
Selection.Cut
Range("K13").Select
ActiveSheet.Paste
Range("B13").Select
End If

GoTo Hledej
End Sub

but here I have a problem....I dont know how to select a range which
values are < "" ....so I select a range (A1:C252) ....

and another problem is that this is not working, cause it crash at line
with "Activesheet.Paste" .... and I dont know why....

could you 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

Try post no: #13


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

I tried it, but there is one problem with it.

I must it run more than once if I want to eliminate all empty cell
between values in my column.

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

Ok here i go again...

Code
-------------------

Sub RmvBlank()
Dim myRngRow As Long, i As Long

myRngRow = Range("D65536").End(xlUp).Row

For i = myRngRow To 1 Step -1
If Cells(i, 4).Value = Empty Then 'i= your row & 4= your column (i.e= 'D')
Cells(i, 4).Delete Shift:=xlUp
End If
Next i

End Sub

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

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

If the cells are really empty there are quicker ways of doing that.

Option Explicit
Sub testme01()
Dim myRng As Range

Set myRng = ActiveSheet.Range("B13:D99")

On Error Resume Next
myRng.Cells.SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
On Error GoTo 0

End Sub

Adjust your range to match what you need.



tommy_gtr wrote:

I tried it, but there is one problem with it.

I must it run more than once if I want to eliminate all empty cells
between values in my column.

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

dear dave and ilyaskazi,

your solutions are very interesting and I like it, but I have stil
some problems with my macro.

ilyaskazi: your macro works very good, but I dont know how can
specify the range in which it may works....cause now it works in whol
column....so it means that after I run it ..... my cell with value
begins from firs row ... but I want that it will works for example onl
in Range A10:A252 .... is it possible???

Dave: your macro is also good, but in my case it doesnt works, cause m
cell has "" value .... so it means that I copy this values from cell
where is function like this : =IF(B9=A9;B9;"") .... so my "empty" cell
are cells with "" value ... and excel recognized it like no empt
cells.

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

I'd fix those values that evaluated to "".

Select the range
edit|replace
what: (leave blank)
with: $$$$$
replace all

followed by:
edit|replace
what: $$$$$
with: (leave blank)
replace all

Then run the other macro.

I don't know what your range is, so I still used B13:d99:

Option Explicit
Sub testme01()
Dim myRng As Range

Set myRng = ActiveSheet.Range("B13:D99")

With myRng
.Replace what:="", replacement:="$$$$$", _
lookat:=xlWhole, searchorder:=xlByRows, MatchCase:=False

.Replace what:="$$$$$", replacement:="", _
lookat:=xlWhole, searchorder:=xlByRows, MatchCase:=False

On Error Resume Next
.Cells.SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
On Error GoTo 0

End With

End Sub


tommy_gtr wrote:

dear dave and ilyaskazi,

your solutions are very interesting and I like it, but I have still
some problems with my macro.

ilyaskazi: your macro works very good, but I dont know how can I
specify the range in which it may works....cause now it works in whole
column....so it means that after I run it ..... my cell with values
begins from firs row ... but I want that it will works for example only
in Range A10:A252 .... is it possible???

Dave: your macro is also good, but in my case it doesnt works, cause my
cell has "" value .... so it means that I copy this values from cells
where is function like this : =IF(B9=A9;B9;"") .... so my "empty" cells
are cells with "" value ... and excel recognized it like no empty
cells.

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

Try one more,


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

Sub RemoveBlank()
Dim myRngRow As Long, i As Long

myRngRow = Range("A252").End(xlUp).Row '252= to end on this row

For i = myRngRow To 10 Step -1 '10= to start from this row
If Cells(i, 1).Value = Empty Then 'i= your row & 1= your column (i.e= 'A')
Cells(i, 1).Delete Shift:=xlUp
End If
Next i

End Sub

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


HTH
ilyaskazi


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

thank you ,

I think both solution will be working .... and are what I need ....

So I thank you very much guys ...

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