PDA

View Full Version : [SOLVED] Search By Region



johnske
09-28-2004, 03:13 AM
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? :bink:



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 :bink:

onlyadrafter
09-28-2004, 03:37 AM
Hello,

How are the current and subsequent ranges determined?

johnske
09-28-2004, 03:58 AM
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.... :dunno

John :bink:

onlyadrafter
09-28-2004, 04:07 AM
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
09-28-2004, 04:35 AM
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
09-28-2004, 04:47 AM
Thanx Jacob,

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

(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 :bink:

Jacob Hilderbrand
09-28-2004, 04:50 AM
You're Welcome

Take Care