copy & paste visible values with VBA

[bademployee]

Hi all,

I'm using this to hide any rows without a value in "deblending" B13:B40:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim r As Range, c As Range
Set r = Range("b13:b40")
Application.ScreenUpdating = False
For Each c In r
    If Len(c.Text) = 0 Then
        c.EntireRow.Hidden = True
    Else
        c.EntireRow.Hidden = False
    End If
Next c
Application.ScreenUpdating = True
End Sub

Leaves a list of values in A13:B40 (will only ever be a maximum number of 7 rows visible, usually only 2-3, but will have hidden rows between each visible row).

What I need is some VBA to copy & paste the visible values in A13:A40 to another sheet called "Blends" C9:C15.

Also, the visible values in B13:B40 copy & pasted to "Blends" R9:R15.

Thanks in advance.

Mark

 

[BobUmlas]

Sub CopyVisible()
'untested
Range("A13:A40").Specialcells(xlcelltypevisible).copy sheets("Blends").range("C9")
Range("B13:B40").specialcells(xlcelltypevisible).Copy Sheets("Blends").Range("R9")
End Sub

 

[bademployee]

Thanks BobUmlas.

I tried incorporating the 2 peices, but get a run-time error:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim r As Range, c As Range
Set r = Range("b13:b40")
Application.ScreenUpdating = False
For Each c In r
    If Len(c.Text) = 0 Then
        c.EntireRow.Hidden = True
    Else
        c.EntireRow.Hidden = False
    End If
Next c
Application.ScreenUpdating = True


[COLOR=#333333]Range("A13:A40").Specialcells(xlcelltypevisible).copy sheets("Blends").range("C9")[/COLOR]
[COLOR=#333333]Range("B13:B40").specialcells(xlcelltypevisible).Copy Sheets("Blends").Range("R9")

[/COLOR]End Sub

Any suggestions?

Mark

 

[manc]

Try BobUmlas's code by itself, see what happens?
It may be that you don't need to integrate it within your code?
Just throwing suggestions out there...

Regards
manc