Solved

Selecting a range

Posted on 2014-12-12
6
67 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:
SimonAdept 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 31

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now