Solved

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

Posted on 2011-03-11
2
244 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 500 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Problem: Word 2016 cannot import a .vsdx created with Visio 2013 6 50
Excel if formula 2 19
how to reset cell styles? 15 24
excel conditional formating 4 14
As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

808 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