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:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,966
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,257
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

omairhe

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

omairhe

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

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,012
Office Version
  1. 2019
Platform
  1. Windows
OH I just hide the first row and this fixed it for some reason. Lol.
Thanks all.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,966
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,024
Messages
5,622,271
Members
415,890
Latest member
Apopolis

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