Defining a variable length area for output.

[JHB]

I haven't done Excel macros for some time, and find myself rusty --
having forgotten some key concepts!

At this point I need to develop a macro to define an area of variable
length which will be contain data to be output for an Access
application. I used to be able to do this, but have lost my previous
creations and just don't recall how to do it.

Say the area is to be defined as ValueOutput, and the area is AA1: AE
(x) where x is the length of the excel table. The length will be
defined the last line containing data in column AA. Hence, if there is
data in the range AA1:AE30, that would be the defined area
ValueOutput. However, the area could just as well be AA1:AE900! I am
trying to do this because Access gets all upset when I try and import
a series of blank records (which would be at the end of the table if I
used a defined set area).

I hope I have made my problem clear, and would appreciate any quick
solutions you may have.

The best I can do is the following (which I found in someone elses
spreadsheet), and it doesn't quite work it gives me AA1:AE1.. I wanted
A1:E133:


Sheets("data").Select
Range("AA1").Select
Set rng = Cells(Columns.Count, 4).End(xlUp)
Range(Range("a1"), rng).Resize(, 1).Name = "ValueOutput"


Thanks again!

John Baker

 

[Dave Peterson]

Dim LastRow as long
dim myRng as range
with worksheets("OutputWorksheetNameHere")
lastrow = .cells(.rows.count,"AA").end(xlup).row
set myrng = .range("aa1").resize(lastrow,5)
'or
set myrng = .range("aa1:AE" & lastrow)
end with

'if you really needed to name that range:
myrng.name = "ValueOutput"

 

[FSt1]

hi
Sub findit()
'just to select it
Dim lr As Long
lr = Cells(Rows.Count, "a").End(xlUp).Row
Range("A1:E" & lr).Select
'or just to find out how big
Dim valueoutput As Range
Set valueoutput = Range("A1:E" & lr)
MsgBox valueoutput.Address
'or to give it a range name
ActiveWorkbook.Names.Add Name:="ValueOutput", RefersTo:= _
Sheets("sheet3").Range("A1:E" & lr)

End Sub
adjust ranges to suit your needs.
regards
FSt1