Solved

Loop until blank value in excel VBA

Posted on 2011-03-19
4
370 Views
Last Modified: 2012-05-11
Hi there,

I ahve the following loop in VBA, but I want to change it so that it finishes whent he first blank cell is hit in the A collumn. THe rowsa are inserted dynamically, so it could be 10 or 100 depending on the sheet. This is what I have so far


Sub GenSheets()


Dim rng    As Range
    Dim cl     As Range
    With Sheets("Quotation")
        Set rng = .Range(.Cells(6, 1), .Cells(.Rows.Count, 1).End(xlUp))
        For Each cl In rng
            
            MsgBox (cl.Value)
            
        Next cl
    End With
    
End Sub

Open in new window

0
Comment
Question by:wellso
[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
4 Comments
 
LVL 24

Expert Comment

by:jimyX
ID: 35172192
You match as follows:
If Not YouRange Is Nothing Then
0
 
LVL 2

Author Comment

by:wellso
ID: 35172231
Could you give me an example how I break out from the loop with it?
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
ID: 35172323
Sub GenSheets()


Dim rng    As Range
    Dim cl     As Range
    With Sheets("Quotation")
        Set rng = .Range(.Cells(6, 1), .Cells(.Rows.Count, 1).End(xlUp))
        For Each cl In rng
            If Len(cl) = 0 Then Exit For
            MsgBox (cl.Value)
           
        Next cl
    End With
   
End Sub

Kevin
0
 
LVL 2

Author Closing Comment

by:wellso
ID: 35172334
cheers buddy
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.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
My experience with Windows 10 over a one year period and suggestions for smooth operation
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

740 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