How to set a Variying Range in vba Excel.

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,040
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:
And an ugly way...

VBA Code:
Sub GetRng()
MsgBox Range(Cells(ActiveCell.Row, "B").End(xlUp), Cells(ActiveCell.Row, "B").End(xlDown)).Address
End Sub
 
Upvote 0

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.
Glad we could help & thanks for the feedback.
 
Upvote 0
And an ugly way...

VBA Code:
Sub GetRng()
MsgBox Range(Cells(ActiveCell.Row, "B").End(xlUp), Cells(ActiveCell.Row, "B").End(xlDown)).Address
End Sub

I am trying to use your code because I have a protected sheet and for some reason the other two codes will prompt me to unprotect for the operation to complete.
I used this in the following manner
VBA Code:
Sub search_a()
   Dim Fnd As Range
   Set Fnd = Range(Cells(ActiveCell.Row, "B").End(xlUp), Cells(ActiveCell.Row, "B").End(xlDown)).Find("a*", , xlValues, xlWhole, , , False, False)
If Not Fnd Is Nothing Then
  Application.Goto Cells(Fnd.Row, ActiveCell.Column)
End If
End Sub
However I need to resize or offset the xlUp because the Find function will take me to second instance of word starting with "a", where in fact it needs to take me to the row above. There is no problem with the bottom row.

so if I run the code in my data sheet B5 = Apple and B6 = Atari
the code will take me to B6 instead of B5
 
Upvote 0
I have tried like this
VBA Code:
   Set Fnd = Range(Cells(ActiveCell.Row, "B").End(xlUp).Offset(-1, 0), Cells(ActiveCell.Row, "B").End(xlDown)).Find("a*", , xlValues, xlWhole, , , False, False)

Although it fixed the problem but I'm not too sure if that is the best way of doing this. I get error when I try to run that code from Row 2 or 3.
 
Upvote 0
OH I just hide the first row and this fixed it for some reason. Lol.
Thanks all.
 
Upvote 0
The 2nd parameter of Find (which you have left blank) is After:=
If you leave it blank then it defaults to the first cell in the range and so it searches After the first cell. You want it to look at the last cell in the range so it loops to the first cell with the After.

VBA Code:
    With Range(Cells(ActiveCell.Row, "B").End(xlUp), Cells(ActiveCell.Row, "B").End(xlDown))
        Set Fnd = .Find("a*", .Cells(.Cells.Count), xlValues, xlWhole, xlByRows, xlNext)
    End With
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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
Back
Top