• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1147
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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