2

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

Community
  • 1
  • 1
account can't delete
  • 47
  • 1
  • 1
  • 8

2 Answers2

2

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
paul bica
  • 10,557
  • 4
  • 23
  • 42
  • @SiddharthRout: I was going to upvote yours... your answers always work because you're explicit with everything – paul bica Sep 06 '15 at 16:58
  • Compile Error: invalid or unqualified reference... Its because of .Rows and. Columns I guess... I tried by removing dot but selecting all visible cells... Please help me What to do? – account can't delete Sep 06 '15 at 16:59
  • That's ok.. Your code is shorter and I like it and hence I deleted mine ;) – Siddharth Rout Sep 06 '15 at 16:59
  • @SudarshanKShetty: please update your question with all relevant code, including the line with the error (make sure to include the first and 3rd line: `With ActiveSheet.UsedRange.Columns(4)` and `End With`) – paul bica Sep 06 '15 at 17:02
  • 1
    There was something bothering me about this code... I was wondering as to why did I use LastRow and then it dawned upon me... Now I know what :D It is the `UsedRange` :) More about `Usedrange` [Here](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) – Siddharth Rout Sep 06 '15 at 17:23
  • Awesome dude... Thank you so much... Its working...One problem its selecting entire column... Can you code it only for the visible cells which contains data less one last cell... – account can't delete Sep 06 '15 at 17:23
  • @SudarshanKShetty: Exactly my thoughts and hence you need to find the last row as shown in my answer – Siddharth Rout Sep 06 '15 at 17:24
  • 1
    There you go: @SiddharthRout's answer provides the fix; I'll update mine as well – paul bica Sep 06 '15 at 17:26
  • As we are excluding the heading... I also want to exclude the last cell which is visible and contains data... I'm sorry I'm asking a lot of follow up questions – account can't delete Sep 06 '15 at 17:29
  • 1
    If you think about it carefully, you will get the answer to your question ;) @SudarshanKShetty – Siddharth Rout Sep 06 '15 at 17:31
  • Ok, I updated it so it selects all visible cells in col D except for the 1st row and the last visible cell with data – paul bica Sep 06 '15 at 17:34
  • 1
    Sorry I changed your code as it was not compiling. Also took care of the scenario where there was data only till Cell A2 :) Feel free to roll back the changes if you want to... – Siddharth Rout Sep 06 '15 at 17:41
1

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
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250