Select Range

D

DominionANG

Having trouble selecting several non-contiguous ranges. The
spreadsheet I am working with is apprx. 300 rows by 110 columns. The
sheet is subtotaled. I need to copy data and move to a summary
worksheet.

There is a subtotaled section entitled 'Risk'. Within this section,
the only data required is Country, Customer, 3Q (orders, revenue,
gross margin), and 4Q (orders, revenue, gross margin); columns B, E,
AQ, AR, AS, BK, BL, and BM - respectively. The section title is in
cell A162 and the data is in A163:CY200.

I would prefer to select all of the data from the section at once, but
help selecting them separately will be greatly appreciated.

Areas of concern:
1.) Section is currently 39 rows, but will change over time.
2.) I do not maintain the spreadsheet. At the end of the month, it is
placed in shared mode. 3Q and 4Q columns could change, i.e., I do not
expect them to remain in AQ, AR, AS, etc.
3.) Must capture all rows of data in 3Q and 4Q groupings for all rows
in Country and Customer. In detail for this month (36 rows), there
are 36 entries for Country and Customer (both are text), but numerical
data in 3Q and 4Q can be null, black, 0, positive, or negative. Using
End(x1Down) only yeilds 2 rows of numerical data for 3Q; 1st row is
blank, 2nd row is negative, and 3rd row is blank. But there is data
in 12 additional rows.

If someone can please help.
BTW - I have not written the Copy/Paste portion because I have not
gotten the selection portion to work.

Sub SelectData()
'
' SelectData Macro
' Macro Selects Data from Detail Tab and Copies to Risks and
Opportunities Tab
' Data Copied Includes Country, Customer, and Quarterly Orders,
Revenue, and Gross Margin
' Macro Written 08/04/2004 by GibbsKJ
'

'
Dim Country As Range
Dim Customer As Range
Dim Quarter3 As Range
Dim Quarter4 As Range
Dim Data As Range
Sheets("2004 Detail").Select
Cells.Find(What:="risk", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range(ActiveCell.Offset(1, 1), ActiveCell.End(xlDown)).Select
Set Country = Range(ActiveCell.Offset(1, 1),
ActiveCell.End(xlDown)).Select
Range(ActiveCell.Offset(1, 4), ActiveCell.End(xlDown)).Select
Set Customer = Range(ActiveCell.Offset(1, 4),
ActiveCell.End(xlDown)).Select
Range(ActiveCell.Offset(1, 42), ActiveCell.Offset(0,
2).End(xlDown)).Select
Set Quarter3 = Range(ActiveCell.Offset(1, 42),
ActiveCell.Offset(0, 2).End(xlDown)).Select
Range(ActiveCell.Offset(1, 62), ActiveCell.Offset(0,
2).End(xlDown)).Select
Set Quarter4 = Range(ActiveCell.Offset(1, 62),
ActiveCell.Offset(0, 2).End(xlDown)).Select
Set Data = Union(Country, Customer, Quarter3, Quarter4)
End Sub

With the following I know there should be 36 rows, so I need a range
that starts at the offset from the reference point and ends at the
Offset(Last RowIndex of rngTest, # of Colums specified). Is this even
possible? Much thanks and gratitude in advance.

Sub RangeTest()
Dim rngTest As Range
Sheets("2004 Detail").Select
Cells.Find(What:="risk", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(1, 1).Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Set rngTest = Selection
MsgBox rngTest.Rows.Count 'number of rows
End Sub
 
Top