Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Defining a variable length area for output.
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Defining a variable length area for output.
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" JHB wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Defining a variable length area for output.
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 "JHB" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable length of input area in a combobox | Excel Discussion (Misc queries) | |||
Combo Box to populate variable output range | Excel Discussion (Misc queries) | |||
defining a variable-size worksheet area for charting | Excel Worksheet Functions | |||
defining a variable-size worksheet area for copying & pasting | Excel Worksheet Functions | |||
Defining UserForm Caption from a Variable | Excel Worksheet Functions |