Can I edit this macro to only copy i=cells that are not hidden?

[tonywatsonhelp]

Hi Everyone,

I have this macro that works great but it copys cells with values even if the row is hidden,

is it possible to copy only cells that are visiable?

If not, or if its easier a macro that clear contents of all hidden rows would be fine

Thanks

Tony

Sub Move_Values1()
Application.ScreenUpdating = False
Sheets("Test1").Select
LastRow1 = Sheets("Test1").Cells(Rows.Count, "A").End(xlUp).Row
If LastRow1 < 10 Then
Exit Sub
End If


Sheets("Test1").Range("J10", "SW" & LastRow1).SpecialCells(xlCellTypeConstants, 23).Select
 For Each Area In Selection.Areas
 AddS = Area.Address

 Sheets("Test2").Range(AddS).Value = Area.Value
 Next Area

End Sub

 

[JustynaMK]

Hi Tony,

Try the following:

Sub Move_Values1()
    Dim lastrow1            As Long
    Dim area                As Range
    Dim AddS                As String
        
    Application.ScreenUpdating = False
    Sheets("Test1").Select
    lastrow1 = Sheets("Test1").Cells(Rows.Count, "A").End(xlUp).Row
    
    If lastrow1 < 10 Then
        Exit Sub
    End If




    Sheets("Test1").Range("J10", "SW" & lastrow1).SpecialCells(xlVisible).Select


    For Each area In Selection
        If Len(area) > 0 Then
            AddS = area.Address
            Sheets("Test2").Range(AddS).Value = area.Value
        End If
    Next area


End Sub

 

[tonywatsonhelp]

Brilliant,
thank you JustynaMk
works perfectly

Tony

 

[JustynaMK]

cool!