copy cell with non null value

[tommy_gtr]

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]

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

[ilyaskazi]

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...

Please Login or Register  to view this content.

Cheers!!!
ilyas

[Jim Cone]

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
news:tommy_gtr.1wj9ed_1128690324.2746@excelforum-nospam.com
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

[Dave Peterson]

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]

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 starts 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

[tommy_gtr]

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

[ilyaskazi]

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 starts 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 value for your row to start.

Please Login or Register  to view this content.

[tommy_gtr]

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

[Dave Peterson]

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]

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

[Dave Peterson]

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

[ilyaskazi]

if u need to remove only blank cells from the particular range then try this

Please Login or Register  to view this content.

[tommy_gtr]

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

[ilyaskazi]

Try post no: #13

[tommy_gtr]

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

[ilyaskazi]

Ok here i go again...

Please Login or Register  to view this content.

[Dave Peterson]

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

[tommy_gtr]

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

[Dave Peterson]

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

[ilyaskazi]

Try one more,

Please Login or Register  to view this content.

HTH
ilyaskazi

[tommy_gtr]

thank you ,

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

So I thank you very much guys ...

tommy