• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 79
  • Last Modified:

Selecting a range

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.
1 Solution
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
    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
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.
To do this in a more VBA way:

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

Open in new window

Rob HensonFinance AnalystCommented:
Or you can do with this logic:

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

Rob H
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.

RWayneHAuthor Commented:
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.
RWayneHAuthor Commented:
Sorry I got it  duh...   PurReqNumber = c      Sorry.  Still testing but I think this will work.
RWayneHAuthor Commented:
Worked great thanks for the help
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now