Solved: Defining variable Ranges?

[CodeMakr]

I need to manipulate a set of data, always knowing the top (starting) point, but the bottom location is always variable (in both rows and columns), so I'm not sure the best way to activate the range to manipulate the contents of it. E.G.,
[VBA]Range("B3", "M1304")[/VBA]
How do I complete the range designation of this statement, when the M1304 portion is always variable??

[Bob Phillips]

[vba]

Dim LastRow As Long
Dim LastCol As Long
LastRow = Cells.Find(What:="*", _
After:=Range("A1"), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
LastCol = Cells.Find(What:="*", _
After:=Range("A1"), _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

Range(Range("B3"), Cells(LastRow, LastCol)).Select
[/vba]

[XLGibbs]

Insert>Names>Define:

Name this range whatever you like and in the Refers To:


=OFFSET(Sheet1!$B$3,0,0,COUNTA(Sheet1!$B:$B),CountA(Sheet1!$3:3))


For example, if you named this range "MyData"

You could refer to it in other formulas, as a pivot table source, or within VBA code..

Range("MyData")

For the formula to work as written no data can be in column A, and no data in rows 1 or 2 in column B. If so, it would need minor adjusting. Also assumes every column B:M in your example would have data in row 3 (like a heading).

Hope that helps.

There are ways in code as well
[vba]
Dim LastRow as Long, LastCol as Long
LastRow = Range(cells(2,3),cells(rows.count,3).end(xlup)).Row

LastCol = Range(cells(lastRow,3),cells(lastRow,columns.count).end(xlToLeft)).Column

[/vba]

[Charlize]

Try this one :
[VBA]Sub endcolumn_endrow()
'last row
Dim lrow As Long
'last column
Dim lcol As Long
'lastrow
lrow = Range("B" & Rows.Count).End(xlUp).Row
'lastcolumn with data in (number of column)
lcol = Sheets(1).Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
'row 3 column 2 , lrow, lcol
Range(Cells(3, 2), Cells(lrow, lcol)).Activate
End Sub[/VBA]
Charlize

[CodeMakr]

Thanks to everyone. I was/am lost on the nomenclature
[VBA]Range(Range("xx"), Cells(etc)

or

Range(Cells(3,2), Cells(etc.)[/VBA]

Kind of funny how I sat here for a long time and your expert analysis/direction solved my problem in seconds