Solved

Use VBA to create a named range from a column of data, exclude blanks from the resulting name.

Posted on 2015-02-11
2
62 Views
Last Modified: 2016-02-10
I want to create a named range by selecting only the cells in a column that contain data,
Data in the column can easily be sorted to move all the cells with  data to the top before defining the name .
0
Comment
Question by:AMixMaster
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 11

Accepted Solution

by:
jkpieterse earned 500 total points
ID: 40605015
(re)Sorting the data will wreck the range name, it will keep pointing to the same cells. The syntax for defining the name is:

intersect(activesheet.usedrange,Range("A:A")).SpecialCells(xlCellTypeVisible).Name = "TheName"
0
 

Author Closing Comment

by:AMixMaster
ID: 40605857
works like a charm
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question