Range - selecting multiple cells

[gr8guy]

Hi,

does anybody know through VBA how to select mutliple cells in different
columns & rows together. the manual method is to press cntrl key & click the
cells.

the code i have written asks for a text input to search similar text(names)
spread in different columns & rows in a worksheet & gives the count for the
no. of names found.

sub findtheperson()
dim WS as worksheet
set WS=Worksheets("Sheet1")
dim sname as string
dim Cnt as Long
Cnt=0

With WS
sname=LCase(Inputbox("Enter the name to Count: "))
With WS.Range("a1:iv65536")
set c = .Find(sname,Lookin:=xlValues)
If Not c Is Nothing Then
firstaddress=c.address
Do
Cnt=Cnt+1
' Cells.SpecialCells(xlCellTypeConstants).Select
Set c= .FindNext(c)
Loop While Not c Is Nothing And c.address <> firstaddress
End If
MsgBox "The Name selected: "& sname &", Name Count: " & Cnt
End With
End With
End sub

This just finds the count of the no of occurences of the similar names in
the worksheet, but does not do a multi-select. Can use ColorIndex property
to color code the found names, but donot want to do that, instead do a
multi-select. Cant use SpecialCells property as it selects all names on the
sheet & also cannot use Union Method as it requires the more than 1 ranges
to be defined. like mymultiRange=Union(Range1,Range2,....)

Any ideas?????????

Rgds,

Eijaz

 

[Ole Michelsen]

You should use union.

Set r1 = range1
Set r2 = range2
Set myselection = Union(r1, r2)

 

[Fer]

Proof this

to be defined. like mymultiRange=Range("D7,D712,F7:F11,E18:F20,H10:I15")

Not Union

Fer

 

[Tom Ogilvy]

You can use union since you are getting references to the found cells (the
variable c)

sub findtheperson()
dim WS as worksheet
Dim c as Range, rng as Range
dim sname as string, firstaddress as String
dim Cnt as Long
Cnt=0
set WS=Worksheets("Sheet1")

Dim rng as Range
sname=LCase(Inputbox("Enter the name to Select: "))
With WS.Cells
set c = .Find(sname,Lookin:=xlValues)
If Not c Is Nothing Then
firstaddress=c.address
Do
Cnt=Cnt+1
if rng is nothing then
set rng = c
else
set rng = Union(rng,c)
end if
Set c= .FindNext(c)
Loop While Not c Is Nothing And c.address <> firstaddress
rng.Select
End If

MsgBox "The Name selected: "& sname &", Name Count: " & Cnt
End With
End sub

 

[BrianB]

This code should do what you want:-
'---------------------------------------------------------
Sub findtheperson()
Dim WS As Worksheet
Dim sname As String
Dim Cnt As Long
Dim RangeList As String
'--------------------------------------
Set WS = Worksheets("Sheet1")
Cnt = 0
RangeList = ""
sname = LCase(InputBox("Enter the name to Count: "))
'-
With WS.Cells
Set c = .Find(sname, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
Cnt = Cnt + 1
RangeList = RangeList & c.Address & ","
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
RangeList = Left(RangeList, Len(RangeList) - 1)
ActiveSheet.Range(RangeList).Select
MsgBox "The Name selected: " & sname & ", Name Count: " & Cnt
End With
End Sub
'-------------------------------------------------------------------

 

[Tom Ogilvy]

This will fail if the string gets too big. Only a concern if there are a
lot of discontiguous cells.

 

[gr8guy]

Thanks Tom,

Instead of taking With WS.Range("a1:iv65536"), With WS.Cells works fine &
better!

yes, i do see the previous solution would fail at one point if there are a
lot of discontagious cells, if string is too large.

But thanks to Brian Also! Thanks a lot!

Thanks a Bunch!

Rgds,

Eijaz