I have a workbook with 2 sheets. Both sheets contain a sparce matrix of
data in which the Column Title row and the first column are the only
gauranteed row and column to be filled. I want to select the entire sparse
matrix.

The code below correctly selects the sparse matrix on one sheet, but when I
switch to the 2nd sheet it selects only the 1st row of the matrix.

Any help would be appreciated.

Option Explicit
Dim TopCell, LstRow, LstCol, LstCell, ActiveData As Range

Sub SelActiveData()
Dim NumCol As Integer
ActiveSheet.Select
'
' Selects active data whe
' TopCell is left most cell of column title row
'
NumCol = 14
Set TopCell = ActiveSheet.Range("A2")
Set LstRow = TopCell.End(xlDown)
Set LstCell = LstRow.Offset(0, NumCol)
Set ActiveData = _
ActiveSheet.Range(TopCell.Offset(1, 0), LstCell)
ActiveData.Select

End Sub

maybe

Sub selactivedata()
With ActiveSheet
lr = Application.Max(3, .Cells(.Rows.Count, "a").End(xlUp).Row)
..Range(.Cells(3, "a"), .Cells(lr, 14)).Select
End With
End Sub

--
Don Guillett
SalesAid Software

"Dave B" wrote in message
...
I have a workbook with 2 sheets. Both sheets contain a sparce matrix of
data in which the Column Title row and the first column are the only
gauranteed row and column to be filled. I want to select the entire sparse
matrix.

The code below correctly selects the sparse matrix on one sheet, but when
I switch to the 2nd sheet it selects only the 1st row of the matrix.

Any help would be appreciated.

Option Explicit
Dim TopCell, LstRow, LstCol, LstCell, ActiveData As Range

Sub SelActiveData()
Dim NumCol As Integer
ActiveSheet.Select
'
' Selects active data whe
' TopCell is left most cell of column title row
'
NumCol = 14
Set TopCell = ActiveSheet.Range("A2")
Set LstRow = TopCell.End(xlDown)
Set LstCell = LstRow.Offset(0, NumCol)
Set ActiveData = _
ActiveSheet.Range(TopCell.Offset(1, 0), LstCell)
ActiveData.Select

End Sub

One way...

Option Explicit
Sub SelActiveData()

dim LastRow as long
Dim LastCol as long

with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
lastcol = .cells(1,.columns.count).end(xltoleft).column

if lastrow = 1 then
msgbox "Only headers"
exit sub 'what should happen?
end if

.range("a2",.cells(lastrow,lastcol).select
end with
end sub

(Untested, so watch for typos.)

ps.

When you do this:

Dim TopCell, LstRow, LstCol, LstCell, ActiveData As Range

ActiveData is declared as a range object. But all the others are declared as
Variants.

But you could use:
Dim TopCell as range, LstRow as range, LstCol as range ....



Dave B wrote:

I have a workbook with 2 sheets. Both sheets contain a sparce matrix of
data in which the Column Title row and the first column are the only
gauranteed row and column to be filled. I want to select the entire sparse
matrix.

The code below correctly selects the sparse matrix on one sheet, but when I
switch to the 2nd sheet it selects only the 1st row of the matrix.

Any help would be appreciated.

Option Explicit
Dim TopCell, LstRow, LstCol, LstCell, ActiveData As Range

Sub SelActiveData()
Dim NumCol As Integer
ActiveSheet.Select
'
' Selects active data whe
' TopCell is left most cell of column title row
'
NumCol = 14
Set TopCell = ActiveSheet.Range("A2")
Set LstRow = TopCell.End(xlDown)
Set LstCell = LstRow.Offset(0, NumCol)
Set ActiveData = _
ActiveSheet.Range(TopCell.Offset(1, 0), LstCell)
ActiveData.Select

End Sub

--

Dave Peterson

This might work... can't say why yours doesn't.

Sub SelActiveData()
Dim LstCol As Long
Dim LstRow As Long

With ActiveSheet
LstCol = .Cells(1, 1).End(xlToRight).Column
LstRow = .Cells(1, 1).End(xlDown).Row
.Range(.Cells(2, 2), .Cells(LstRow, LstCol)).Select
End With
End Sub

Scott

Dave B wrote:
I have a workbook with 2 sheets. Both sheets contain a sparce matrix of
data in which the Column Title row and the first column are the only
gauranteed row and column to be filled. I want to select the entire sparse
matrix.

The code below correctly selects the sparse matrix on one sheet, but when I
switch to the 2nd sheet it selects only the 1st row of the matrix.

Any help would be appreciated.

Option Explicit
Dim TopCell, LstRow, LstCol, LstCell, ActiveData As Range

Sub SelActiveData()
Dim NumCol As Integer
ActiveSheet.Select
'
' Selects active data whe
' TopCell is left most cell of column title row
'
NumCol = 14
Set TopCell = ActiveSheet.Range("A2")
Set LstRow = TopCell.End(xlDown)
Set LstCell = LstRow.Offset(0, NumCol)
Set ActiveData = _
ActiveSheet.Range(TopCell.Offset(1, 0), LstCell)
ActiveData.Select

End Sub

Thanks Don & Scott. Both solutions worked. Being new to VBA it's good
experience to see different approaches.

"Don Guillett" wrote in message
...
maybe

Sub selactivedata()
With ActiveSheet
lr = Application.Max(3, .Cells(.Rows.Count, "a").End(xlUp).Row)
.Range(.Cells(3, "a"), .Cells(lr, 14)).Select
End With
End Sub

--
Don Guillett
SalesAid Software

"Dave B" wrote in message
...
I have a workbook with 2 sheets. Both sheets contain a sparce matrix of
data in which the Column Title row and the first column are the only
gauranteed row and column to be filled. I want to select the entire
sparse matrix.

The code below correctly selects the sparse matrix on one sheet, but when
I switch to the 2nd sheet it selects only the 1st row of the matrix.

Any help would be appreciated.

Option Explicit
Dim TopCell, LstRow, LstCol, LstCell, ActiveData As Range

Sub SelActiveData()
Dim NumCol As Integer
ActiveSheet.Select
'
' Selects active data whe
' TopCell is left most cell of column title row
'
NumCol = 14
Set TopCell = ActiveSheet.Range("A2")
Set LstRow = TopCell.End(xlDown)
Set LstCell = LstRow.Offset(0, NumCol)
Set ActiveData = _
ActiveSheet.Range(TopCell.Offset(1, 0), LstCell)
ActiveData.Select

End Sub

Glad to help.

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
maybe

Sub selactivedata()
With ActiveSheet
lr = Application.Max(3, .Cells(.Rows.Count, "a").End(xlUp).Row)
.Range(.Cells(3, "a"), .Cells(lr, 14)).Select
End With
End Sub

--
Don Guillett
SalesAid Software

"Dave B" wrote in message
...
I have a workbook with 2 sheets. Both sheets contain a sparce matrix of
data in which the Column Title row and the first column are the only
gauranteed row and column to be filled. I want to select the entire
sparse matrix.

The code below correctly selects the sparse matrix on one sheet, but when
I switch to the 2nd sheet it selects only the 1st row of the matrix.

Any help would be appreciated.

Option Explicit
Dim TopCell, LstRow, LstCol, LstCell, ActiveData As Range

Sub SelActiveData()
Dim NumCol As Integer
ActiveSheet.Select
'
' Selects active data whe
' TopCell is left most cell of column title row
'
NumCol = 14
Set TopCell = ActiveSheet.Range("A2")
Set LstRow = TopCell.End(xlDown)
Set LstCell = LstRow.Offset(0, NumCol)
Set ActiveData = _
ActiveSheet.Range(TopCell.Offset(1, 0), LstCell)
ActiveData.Select

End Sub

Don, your solution works well, but I would also like to assign the range to
a global variable for use in other macros.

I've tried several things (inside and outside the with clause) without
success. the latest being
'
'
'.Range(.Cells(3, "a"), .Cells(lr, 15)).Select
end with
ActiveData = ActiveSheet.Range(Cells(3, "a"), Cells(lr, 15))
end sub

tried ActiveData dim as range and as variant

Thanks again

"Don Guillett" wrote in message
...
Glad to help.

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
maybe

Sub selactivedata()
With ActiveSheet
lr = Application.Max(3, .Cells(.Rows.Count, "a").End(xlUp).Row)
.Range(.Cells(3, "a"), .Cells(lr, 14)).Select
End With
End Sub

--
Don Guillett
SalesAid Software

"Dave B" wrote in message
...
I have a workbook with 2 sheets. Both sheets contain a sparce matrix of
data in which the Column Title row and the first column are the only
gauranteed row and column to be filled. I want to select the entire
sparse matrix.

The code below correctly selects the sparse matrix on one sheet, but
when I switch to the 2nd sheet it selects only the 1st row of the
matrix.

Any help would be appreciated.

Option Explicit
Dim TopCell, LstRow, LstCol, LstCell, ActiveData As Range

Sub SelActiveData()
Dim NumCol As Integer
ActiveSheet.Select
'
' Selects active data whe
' TopCell is left most cell of column title row
'
NumCol = 14
Set TopCell = ActiveSheet.Range("A2")
Set LstRow = TopCell.End(xlDown)
Set LstCell = LstRow.Offset(0, NumCol)
Set ActiveData = _
ActiveSheet.Range(TopCell.Offset(1, 0), LstCell)
ActiveData.Select

End Sub