[COLOR="Navy"]Sub[/COLOR] MG11Sep08
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] A [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] W [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] RwMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] ColNum [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] oMin [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("A4:C10,D4:F18,G4:I9")
oMin = Rng.Count
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] A [COLOR="Navy"]In[/COLOR] Rng.Areas
oMin = Application.Min(A(1).Row, oMin)
c = c + A.Columns.Count
[COLOR="Navy"]For[/COLOR] Col = 1 To A.Columns.Count
W = W & "25,"
[COLOR="Navy"]Next[/COLOR] Col
RwMax = Application.Max(A.Rows.Count, RwMax)
ColNum = ColNum + A.Columns.Count
[COLOR="Navy"]Next[/COLOR] A
ReDim Ray(1 To RwMax, 1 To ColNum)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
Ray(Dn.Row - oMin + 1, Dn.Column) = Dn
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]With[/COLOR] ListBox1
.ColumnWidths = W
.ColumnCount = c
.List = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Private [COLOR=navy]Sub[/COLOR] Worksheet_Activate()
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Ray [COLOR=navy]As[/COLOR] Variant
[COLOR=navy]Dim[/COLOR] Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] A [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] W [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] Col [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] RwMax [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] ColNum [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] oMin [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]With[/COLOR] Sheets("Sheet68")
[COLOR=navy]Set[/COLOR] Rng = .Range("A4:C10,D4:F18,G4:I9")
[COLOR=navy]End[/COLOR] With
oMin = Rng.Count
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] A [COLOR=navy]In[/COLOR] Rng.Areas
oMin = Application.Min(A(1).Row, oMin)
c = c + A.Columns.Count
[COLOR=navy]For[/COLOR] Col = 1 To A.Columns.Count
W = W & "25,"
[COLOR=navy]Next[/COLOR] Col
RwMax = Application.Max(A.Rows.Count, RwMax)
ColNum = ColNum + A.Columns.Count
[COLOR=navy]Next[/COLOR] A
ReDim Ray(1 To RwMax, 1 To ColNum)
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
Ray(Dn.Row - oMin + 1, Dn.Column) = Dn
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]With[/COLOR] ListBox1
.ColumnWidths = W
.ColumnCount = c
.List = Ray
[COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
[COLOR="Navy"]Sub[/COLOR] MG12Sep22
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("A4:C10,D4:F18,G4:I9")
ReDim Ray(1 To Rng.Count)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
c = c + 1
Ray(c) = Dn
[COLOR="Navy"]Next[/COLOR] Dn
ListBox1.List = Application.Transpose(Ray)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]