Solved

Selecting a range

Posted on 2014-12-12
6
71 Views
Last Modified: 2014-12-15
I would like to select all cells to put in a For Selection loop.  How do I start in cell C2 and search down for the first non blank cell and select everything in that range.  I have an issue when there is only one value and it is in C2, xlDown selects all the way down to line 1048576.  How can I select the range so it picks the range, even when there is only one cell there to select in the range?  I have seem some solutions that use xlUp? but I need it to select the range beginning in cell C2, so the header is left out.  Yes it is a list and there is no cell that are blank in-between.  Please advise and thanks.
0
Comment
Question by:RWayneH
6 Comments
 
LVL 18

Accepted Solution

by:
Simon earned 500 total points
ID: 40497272
This works for me. The end(xldown) is OK if there is a value in C2, but not if there is only a value in C1.
Sub test()
Dim rng As Range
If IsEmpty(Cells(2, 3)) Then
    Set rng = Range(Cells(2, 3), Cells(2, 3))
   'OR, you might want to 'Exit sub' here instead
Else
    Set rng = Range(Cells(2, 3), Columns(3).End(xlDown))
End If
Debug.Print rng.Address

For Each c In rng
    'Do something more useful than debug.print here
    Debug.Print c.Address & ":" & c.Value
Next
End Sub

Open in new window


The .end(xlup) method is good if you need to find the last value in a column, but not the first blank cells down from the top.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 40499332
To do this in a more VBA way:

Dim row As Integer
Dim value As String
row=2
Do
   value = cells(row, 3).Value' 3=column C
   if value = "" then
      Exit Do
   End If
   row = row + 1
Loop
Range("C2:C" & (row-1)).Select

Open in new window

0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40500293
Or you can do with this logic:

xlDown
If Row = 1048576 then xlUp
LastRow = ActiveCell.Row
Range("C2:C" & LastRow).Select

Thanks
Rob H
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 

Author Comment

by:RWayneH
ID: 40501208
I am leaning toward the solution from SimonAdept:  however in the For/next, how would I set whatever value of c is to a variable? so I can use it somewhere else.  I am viewing the Locals area while it runs and c is = to Empty.  How world I name c?  For example I would like c to = PurReqNumberValue, so it can be in the 'Do something more useful than debug.print here.  When I add more values in column c it appears to loop thru them, but not sure what value or row it is on.  Please advise and thanks.
0
 

Author Comment

by:RWayneH
ID: 40501222
Sorry I got it  duh...   PurReqNumber = c      Sorry.  Still testing but I think this will work.
0
 

Author Closing Comment

by:RWayneH
ID: 40501473
Worked great thanks for the help
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
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…

830 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