Search By Region

[johnske]

Hi all,

The nuts n bolts of what I want to do is have code that finds & selects the 1st REGION on a sheet where there are any entries, then ask the user if this is the region required & (if NO) the code then resumes the search, finds and selects the next region (etc) until the required region is found...

Can anyone fill in the blanks for me please?

Sub SelectNewRegion()
'//code to find the current region
ActiveCell.CurrentRegion.Select
Reply = MsgBox("Is this the region?", vbYesNo)
If Reply = vbNo Then '//?(code for loop to find and select a new region & ask again)?
End Sub

T muchly IA,
John

[onlyadrafter]

Hello,

How are the current and subsequent ranges determined?

[johnske]

Hello,

How are the current and subsequent ranges determined?

Well, assuming the search starts from row1 column1, I suppose the most efficient search would be first to the right and then down (as A to IV is much less than 1 to 65000+) - but anything that solves the problem....

John

[onlyadrafter]

Hello,


How about this

Sub range_select()
Range("A1").Select
MY_COLS = ActiveSheet.UsedRange.Columns.Count - 1
MY_ROWS = ActiveSheet.UsedRange.Rows.Count - 1
Range(Selection, Range("A1").Offset(MY_ROWS, MY_COLS)).Select
End Sub

This should select all cells used.

[Jacob Hilderbrand]

Try this:

Option Explicit
 
Sub SearchAreas()
Dim FirstAddress As String
Dim c As Range
Dim Rng1 As Range
Dim SelectedRange As Range
Dim MyResponse As VbMsgBoxResult
With ActiveSheet.Cells
Set c = .Find(What:="*", LookIn:=xlValues)
If c Is Nothing Then
MsgBox "The worksheet is empty", vbInformation, "Blank Worksheet"
Exit Sub
End If
c.CurrentRegion.Select
MyResponse = MsgBox("Is this the current region?", _
vbQuestion + vbYesNo, "Current Region")
If MyResponse = vbYes Then
Set SelectedRange = Selection
GoTo Completed:
End If
FirstAddress = c.Address
Set Rng1 = Selection
Do
If Intersect(Rng1, Selection) Is Nothing Then
MyResponse = MsgBox("Is this the current region?", _
vbQuestion + vbYesNo, "Current Region")
If MyResponse = vbYes Then
Set SelectedRange = Selection
GoTo Completed:
End If
End If
If Rng1 Is Nothing Then
Set Rng1 = Selection
Else
Set Rng1 = Union(Rng1, Selection)
End If
Set c = .FindNext(c)
c.CurrentRegion.Select
Loop While Not c Is Nothing And c.Address <> FirstAddress
End With
Completed:
If MyResponse = vbYes Then
MsgBox "You selected range " & SelectedRange.Address, _
vbInformation, "Range Selected"
Else
MsgBox "You did not select a range", vbInformation, "No Range Selected"
End If
End Sub

[johnske]

Thanx Jacob,

That's EXACTLY what I was looking for, it worx perfectly...

(I was trying to keep it much too simple, but from what you've done I can see that was entirely the wrong approach)

Thanks to onlyadrafter for your time too, and nice to 'see' a new face at VBAX...HI!

Regards,
John

[Jacob Hilderbrand]

You're Welcome

Take Care