How to set a Variying Range in vba Excel.

[omairhe]

Hello all,

How do I set special range in the below vba. Currently the range is set to entire column B.

   Set Fnd = Range("B:B").Find("b*", , xlValues, xlWhole, , , False, False)

How do I set the range to be column B but the row should begin from first data cell after blank cell availabe at the top direction from
the active row and til the first blank cell at the bottom direction from the same active row.

To explain if my current active row is 10 and column B5 to B15 are all data cells but B4 and B16 are blank cells , then
I want the range to be B5 to B15. And if my active row of the same sheet is 20 and B17 to B30 are all data cells,
except B16 like before is blank and so is B31, hence my range this time should be B17 to B30.

How do I achieve this please??

Will appreciate a lot and thanks.

 

[mumps]

Does this help?

Sub test()
    MsgBox Range("B" & ActiveCell.Row).CurrentRegion.Address
End Sub

 

[Fluff]

Will column A be totally blank or will there be data in there?

 

[mumps]

@Fluff
Thanks for picking up on that. I guess both columns A and C will have to be blank in order for my suggestion to work.

 

[omairhe]

Does this help?

Sub test()
    MsgBox Range("B" & ActiveCell.Row).CurrentRegion.Address
End Sub

Thanks a lot, this will do..

 

[mumps]

Glad we could help.

 

[omairhe]

Will column A be totally blank or will there be data in there?

although what substitue is for this if the column A and perhaps other columns are not entirely empty?

 

[Fluff]

How about

   Dim Rng As Range
   Set Rng = Range("B" & ActiveCell.Row).CurrentRegion.Columns("B")

But if col A is blank this will not work.

 

[Fluff]

This should work regardless

   Dim Rng As Range
   Set Rng = Range("B" & ActiveCell.Row).CurrentRegion
   If Split(Rng.Address(1, 0), "$")(0) = "A" Then
      Set Rng = Rng.Columns(2)
   Else
      Set Rng = Rng.Columns(1)
   End If

 

[omairhe]

How about

   Dim Rng As Range
   Set Rng = Range("B" & ActiveCell.Row).CurrentRegion.Columns("B")

But if col A is blank this will not work.

Yes, now I have the option to go with either of the vba depending on the situation.

Thanks again both for yours valuable input..