62052

vba code to select only visible cells in specific column except heading

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

Answer1:

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

Answer2:

Is this what you are trying? This will select all visible cells <strong>till the last row</strong> 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

Recommend

  • How to display hh:mm:ss in Excel?
  • Type Mismatch Error (Array): parsing JSON array of strings in VBA
  • Finding Multiple text in Excel
  • Replace data in an Excel file using macros
  • Single django queryset to get n adjacent items
  • incomplete type 'struct' error in C
  • Double-click autofill - dynamic based on adjacent cell
  • xtable - background colour of added rows
  • cell spacing in div table
  • Julia: How to give multiple workers access to functions that are 'include(…)' into a modul
  • how to set variables in a php include file?
  • quiver not drawing arrows just lots of blue, matlab
  • Center align outputs in ipython notebook
  • Suppressing passwd when calling sqlplus from shell script
  • saving file generated by TCPDF
  • How to view images from protected folder with php?
  • Excel's Macro-Recorder usage
  • custom UITableViewCell with image for highlighting
  • How to define custom class, title, and target in Link Browser for content elements and the new rte_c
  • How do I access an unhandled exception in an MVC Error view?
  • Textfile Structure (tables)
  • How to define and use opencv mat of user type
  • print() is showing quotation marks in results
  • Content-Length header not returned from Pylons response
  • Play WS (2.2.1): post/put large request
  • Google Custom Search with transparent background
  • Extracting HTML between tags
  • How to access EntityManager inside Entity class in EJB3
  • Retrieving value from sql ExecuteScalar()
  • C# - Is there a limit to the size of an httpWebRequest stream?
  • Excel - Autoshape get it's name from cell (value)
  • Sending data from AppleScript to FileMaker records
  • Do I've to free mysql result after storing it?
  • Transpose CSV data with awk (pivot transformation)
  • embed rChart in Markdown
  • Sorting a 2D array using the second column C++
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • How can I use `wmic` in a Windows PE script?
  • Unable to use reactive element in my shiny app
  • To Get the radio button value in ruby on rails