Macro that selects a dynamic range until the cell value turns 0

[raku]

Hi

I need a macro that would do a range selection based on the call value.

my starting cell is A240. I have to select all the cells in column A until it sees a cell value 0. As soon as the cell value 0 is encountered the selection should only select until the previous cell. And then it should select up to the H column. For eg if the cell value in say A260 is zero then then I should be able to select the range A240:H259.

Thanks

 

[Marcelo Branco]

Try this

Sub selRange()
    Dim rngFound As Range
    
    Set rngFound = Range(Range("A240"), Range("A240:A" & Cells(Rows.Count, "A") _
    .End(xlUp).Row).Find(What:=0, LookAt:=xlWhole).Offset(-1, 7))
    
    rngFound.Select
    
End Sub

HTH

M.

 

[raku]

Thank you so much for the solution

I got this following error:

Run-time error '91':


Object variable or With block variable not set

 

[Marcelo Branco]

When there is no 0 (zero) in column A this error-message appears

Try this new version

Sub selRange()
    Dim rngFound As Range
    
    Set rngFound = Range("A240:A" & Cells(Rows.Count, "A") _
    .End(xlUp).Row).Find(What:=0, LookAt:=xlWhole)
    
    If Not rngFound Is Nothing Then
        Set rngFound = Range(Range("A240"), rngFound.Offset(-1, 7))
        rngFound.Select
    Else
        MsgBox "Not found 0 in column A"
    End If
    
End Sub

 

[raku]

hi there, your code worked like a charm!

I have another issue of handling the zero in some sheets. The cells that had the value 0 typed in had no issued in being detected. However some of the cells reference to another cell for values. Like the cell value in A263 is 0 which is obtained by referencing to cell A115 (I have the formula =A115 typed in cell A263). So i tried what:="0" instead of just what:=0 in your code. But it still doesnt detects and goes in to the else loop.

Thanks!

 

[Marcelo Branco]

Try changing this code-line

Set rngFound = Range("A240:A" & Cells(Rows.Count, "A") _
    .End(xlUp).Row).Find(What:=0, LookAt:=xlWhole)

To

Set rngFound = Range("A240:A" & Cells(Rows.Count, "A") _
    .End(xlUp).Row).Find(What:=0, LookAt:=xlWhole, [B]LookIn:=xlValues[/B])

M.

 

[raku]

beautiful!

it works now, i owe you a ton

 

[Marcelo Branco]

beautiful!

it works now, i owe you a ton

You are welcome and tks for the feedback

M.