[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1123
  • Last Modified:

Using xlDown method for visible cells to set range object

Hi Experts,

I suspect that the method that I am using to set a range object is not working properly because I am getting unpredictable results.

I'm trying to use the statement below in a filtered table but I am only interested in looping through the visible cells.
ActiveWorkbook.Sheets("DropDown_Controls").Range(ActiveCell, ActiveCell.End(xlDown)) 

Open in new window

I suspect something is wrong because the loop carries on past the number of visible cells.

How can I select only the visible cells as a range using the xlDown method?

Thanks,

OS
0
onesegun
Asked:
onesegun
1 Solution
 
nutschCommented:
ActiveWorkbook.Sheets("DropDown_Controls").Range(ActiveCell, ActiveCell.End(xlDown)).specialcells(xlCellTypeVisible)
0
 
patrickabCommented:
xlDown is generally unreliable as it stops when there is a blank cell. Instead it's better to use .cells.rows.count.End(xlup) as that works up from the last row in the worksheet.
0
 
onesegunAuthor Commented:
Hi patrickab,

Your point is equally valid btw. In this case there is no possiblity of blanks.

Thanks,

OS
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now