asked on

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.

ASKER CERTIFIED SOLUTION

Simon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rspahitz

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

Rob Henson

Or you can do with this logic:

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

Thanks
Rob H

RWayneH

ASKER

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.

RWayneH

ASKER

Sorry I got it duh... PurReqNumber = c Sorry. Still testing but I think this will work.

RWayneH

ASKER

Worked great thanks for the help