How to set a Variying Range in vba Excel.

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,012
Office Version
  1. 2019
Platform
  1. Windows
Hello all,

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

VBA Code:
   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.

1607175684146.png


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.
 
Last edited:

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
Does this help?
VBA Code:
Sub test()
    MsgBox Range("B" & ActiveCell.Row).CurrentRegion.Address
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,613
Office Version
  1. 365
Platform
  1. Windows
Will column A be totally blank or will there be data in there?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
@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

Well-known Member
Joined
Mar 26, 2009
Messages
2,012
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Does this help?
VBA Code:
Sub test()
    MsgBox Range("B" & ActiveCell.Row).CurrentRegion.Address
End Sub
Thanks a lot, this will do..
 

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,012
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,613
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
   Dim Rng As Range
   Set Rng = Range("B" & ActiveCell.Row).CurrentRegion.Columns("B")
But if col A is blank this will not work.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,613
Office Version
  1. 365
Platform
  1. Windows
This should work regardless
VBA Code:
   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
 
Solution

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,012
Office Version
  1. 2019
Platform
  1. Windows
How about
VBA Code:
   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..
 

Watch MrExcel Video

Forum statistics

Threads
1,127,711
Messages
5,626,427
Members
416,183
Latest member
IanA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top