Coping only cells with value?

[pruleone]

Hi

I have VBA command:

Private Sub CommandButton1_Click()
Dim LR As Long
Range("s3:s12").Copy
LR = Range("t" & Rows.Count).End(xlUp).Row + 1
With Range("t" & LR)
.PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False
End Sub

Problem is that in Range("s3:s12") I have also empty cell but I don't want them to be copied into column "t".

How to update this command so that it would copy into column "t" only those cells from column "s" which have value (are higher then 0)?

 

[SkipVought]

hi,

Empty cells: is it the case that you have intermintent cells in the range with no data, or is it that you have contiguous cells containing data and ALL the following cells in the range are empty?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PHV]

What about this ?
.PasteSpecial Paste:=xlPasteValues, SkipBlanks:=True

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[pruleone]

Thank you for replies

.PasteSpecial Paste:=xlPasteValues, SkipBlanks:=True

Didn't work


In column "s" are listed "things" as this:
Apple

Plum

Pear

And I want them to be listed in column "t" as this:
Apple
Plum
Pear

 

[SkipVought]

What you want is not what you asked! Copy ' n' Paste does not work that way! You must learn to ask the right questions if you want the right answers.

You will have to LOOP thru the range, checking each value before assigning a value in column T.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pruleone]

To be honest I was more then sure that I'm asking wrong thing.
Otherwise I think I had found solution from google as well . I just thought that this copy-paste might be solution.

But if to go then back to start then I have UserForm from which user can choose (using check boxes) what he likes.
There are six option:
Apple
Orange
Plum
Pineapple
Pear
Lemon

By pressing OK those "things" which where checked should be wrote down into excel column (lets say column "t").


I run out of ideas how to solve it (as copy-paste is not working).

 

[SkipVought]

I just thought that this copy-paste might be solution.

That would be like someone asking advice about carpentry like how to sharpen the screwdriver blade, (when they thought that chiseling a mortise for a hinge MIGHT be accomplished with a screwdriver), when THAT is not the real question.

Glad you found a solution. For the benefit of other members, you might post the solution you found

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pruleone]

I used following code / solution:

Range("s3:s12").SpecialCells(xlCellTypeConstants).Copy 
Range("t3:t12").PasteSpecial Paste:=xlPasteValues 
Application.CutCopyMode = False

 

[Andrzejek]

Based on your 10 Jan 14 8:52 post.
User Form with checkboxes: Apple, Orange, Plum, ect. and a command button cmdOK:

Option Explicit

Private Sub cmdOK_Click()
Dim c As MSForms.Control

For Each c In Me.Controls
    If TypeOf c Is MSForms.CheckBox Then
        If c.Value Then
            Call WriteToCol(c.Caption)
        End If
    End If
Next c

End Sub

Private Sub WriteToCol(ByRef str As String)
Dim i As Integer

i = 1
Do Until Range("T" & i).Value = ""
    i = i + 1
Loop
Range("T" & i).Value = str

End Sub

Have fun.

---- Andy

 

[SkipVought]

Andy, your obsequiousness is saccharine. ;-)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]