Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Selecting a range

Posted on 2014-12-12
6
Medium Priority
?
76 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
[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
6 Comments
 
LVL 18

Accepted Solution

by:
Simon earned 2000 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

704 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