Ned help with Resize function in Excell VBA

[Guest]

hi experts.

[ A ] [ B ]
1] a
2] b 5
3] c
4] d
5] e 7
6] f 8
7] g 11
8] h 9
9] i
10] j
11] k 4
12] l 8
--------------------------------------
Sub Makro1()
Dim r,c,x

Range("A1:B12").Select
Selection.SpecialCells(xlCellTypeConstants, 1).Offset(0, -1).Select
c = Selection.Address
r = Selection.Rows.Count
c = Selection.Columns.Count

Selection.Resize( , c + 1 ).Select
x=Selection.Address

End Sub
--------------------------------------
I have problem with the resize line.
I want to put address in variable x for both column A and B
where column B have value

so in the end x =$A$2:$B$2,$A$5:$B$8,$A$11:$B$12

how do i do that ? thanks in advance

 

[Bob Phillips]

Sub Makro1()
Dim r, c, x
Dim oArea As Range
Dim rng As Range

Range("A1:B12").Select
Selection.SpecialCells(xlCellTypeConstants, 1).Offset(0, -1).Select
c = Selection.Address
For Each oArea In Selection.Areas
If rng Is Nothing Then
Set rng = oArea.Resize(, 2)
Else
Set rng = Union(rng, oArea.Resize(, 2))
End If
Next oArea

x = rng.Address

End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

just what i neded Bob great job - thanks