vba code to select only visible cells in specific

Question

I need code to select only visible cells in a specific column except the heading.

This is what I tried:

ActiveSheet.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy

But the above code will select the entire column except the heading. I need it to only select a specific column (use column D for your answers).

Answer 1

This will select all visible cells with data in column D, except header and last visible cell with data:

Option Explicit

Sub SelectVisibleInColD()
    Dim lRow As Long

    With ActiveSheet

        lRow = .Cells(.Rows.Count, 4).End(xlUp).Row

        If lRow < 3 Then Exit Sub

        .Cells(1, 4).Offset(1, 0).Resize(lRow - 2).SpecialCells(xlCellTypeVisible).Select

    End With
End Sub

Answer 2

Is this what you are trying? This will select all visible cells till the last row in column A except the header:

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~>  I also want to exclude the last cell which is visible and contains data
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row - 1 

        If lRow < 3 Then Exit Sub

        Debug.Print Intersect( _
                              .Range("A2:A" & lRow), _
                              .Range("A1").Offset(1, 0).SpecialCells(xlCellTypeVisible) _
                              ).Address
    End With
End Sub