?
Solved

Loop through column when naming ranges, but skipping empty cells until end of loop.

Posted on 2011-03-11
2
Medium Priority
?
259 Views
Last Modified: 2012-05-11
I have the below code and with the help of Sid, it works as I wish.  However, now I need for it to actually go through the i = 6 to 100, but if it encounters a blank cell, simply go to the next cell.  Between 6 and 100 are several blank fields separating data groupings, but I need to be sure the all non-blank fields are used.  
With Worksheets("Datasource")
    For i = 6 To 15
        .Range(strColumnRange & i).Name = .Range(strColumnRangeText & i).Value
    Next
End With

Open in new window

0
Comment
Question by:ssmith94015
2 Comments
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 2000 total points
ID: 35109967
Like this?

With Worksheets("Datasource")
    For i = 6 To 100
        If Len(Trim(.Range(strColumnRangeText & i).Value)) <> 0 Then _
        .Range(strColumnRange & i).Name = .Range(strColumnRangeText & i).Value
    Next
End With

Open in new window


Sid
0
 

Author Closing Comment

by:ssmith94015
ID: 35110080
Yes that works.  Thank you again.  It has been a long time since I programmed Excel and your help has been signifcant on this project.  I normally program databases, but the client wants all this in Excel and it has been a challenge!
0

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.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
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.

569 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