PDA

View Full Version : Solved: Defining variable Ranges?



CodeMakr
01-03-2007, 03:48 PM
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.,
Range("B3", "M1304")
How do I complete the range designation of this statement, when the M1304 portion is always variable??

Bob Phillips
01-03-2007, 04:13 PM
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

XLGibbs
01-03-2007, 04:17 PM
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

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

Charlize
01-03-2007, 04:17 PM
Try this one :
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
Charlize

CodeMakr
01-03-2007, 04:28 PM
Thanks to everyone. I was/am lost on the nomenclature
Range(Range("xx"), Cells(etc)

or

Range(Cells(3,2), Cells(etc.)

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