Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I select a range using VBA?
Hi,
wondered if any of you kind souls could help me? I'm writing a macro to take certain columns (containg last name, first name and ID number) from one worksheet and pastes them into another. This "table" is then sorted by ID number, although some of the cells don't contain numbers and the sort places them at the bottom end of the list, so far so good. I've got a sub to search through the ID number list until it finds the first non-numeric entry and I want to then create a selection from that cell back to the first cell of the table (C4 in this case) and would imagine it's something like Range("currentcell:C4").Select but for some reason I can't work out how to do this, and it's probably so simple that I'll kick myself! Oh, by the way, this procedure is to be run weekly and the cell address of the first non-numeric entry will change every time which is why i can't just specify a cell address. Help! Thanks, H. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I select a range using VBA?
Hi
in nearly all cases a Select is not required. e.g. instead of something like Range("A1:D1").select selection.copy you could write Range("A1:D1").copy But if you wa<nt to select something and you want to select everything from C1 to your activecell one way would be: Range("C1:C" & activecell.row).select You may post your current code if you like. There may be better ways without using select statements :-) -----Original Message----- Hi, wondered if any of you kind souls could help me? I'm writing a macro to take certain columns (containg last name, first name and ID number) from one worksheet and pastes them into another. This "table" is then sorted by ID number, although some of the cells don't contain numbers and the sort places them at the bottom end of the list, so far so good. I've got a sub to search through the ID number list until it finds the first non-numeric entry and I want to then create a selection from that cell back to the first cell of the table (C4 in this case) and would imagine it's something like Range("currentcell:C4").Select but for some reason I can't work out how to do this, and it's probably so simple that I'll kick myself! Oh, by the way, this procedure is to be run weekly and the cell address of the first non-numeric entry will change every time which is why i can't just specify a cell address. Help! Thanks, H. . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I select a range using VBA?
Howard,
It's as simple as this: Range(activecell,"c4").Select However, you don't need to select the range, but can use Range(activecell,"c110").copy And before you get too hooked on the activecell thing, you might be better off with something like Range(RANGE("C4").End(xlDown),"C4").Copy In general terms, code that actually selects things runs massively slower than code that just references a particular range. If you use the macro recorder you see the selection, e.g. range("a1").select selection.font.bold = true efficient code would read range("a1").font.bold = true HTH, Robin Hammond www.enhanceddatasystems.com "Howard" wrote in message ... Hi, wondered if any of you kind souls could help me? I'm writing a macro to take certain columns (containg last name, first name and ID number) from one worksheet and pastes them into another. This "table" is then sorted by ID number, although some of the cells don't contain numbers and the sort places them at the bottom end of the list, so far so good. I've got a sub to search through the ID number list until it finds the first non-numeric entry and I want to then create a selection from that cell back to the first cell of the table (C4 in this case) and would imagine it's something like Range("currentcell:C4").Select but for some reason I can't work out how to do this, and it's probably so simple that I'll kick myself! Oh, by the way, this procedure is to be run weekly and the cell address of the first non-numeric entry will change every time which is why i can't just specify a cell address. Help! Thanks, H. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I select a range using VBA?
Thanks Frank, I'll give it a whirl.
Cheers, H. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I select a range using VBA?
To select your range: (sub with your specifics)
Windows("Source Worksheet.xls").Activate range("A1:K21").Select Selection.Copy Windows("Destination Workbook.xls").Activate range("A1").Select ActiveSheet.Paste ----Original Message----- Hi, wondered if any of you kind souls could help me? I'm writing a macro to take certain columns (containg last name, first name and ID number) from one worksheet and pastes them into another. This "table" is then sorted by ID number, although some of the cells don't contain numbers and the sort places them at the bottom end of the list, so far so good. I've got a sub to search through the ID number list until it finds the first non-numeric entry and I want to then create a selection from that cell back to the first cell of the table (C4 in this case) and would imagine it's something like Range("currentcell:C4").Select but for some reason I can't work out how to do this, and it's probably so simple that I'll kick myself! Oh, by the way, this procedure is to be run weekly and the cell address of the first non-numeric entry will change every time which is why i can't just specify a cell address. Help! Thanks, H. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can change range to select active rows instead of :=Range("S10 | Excel Discussion (Misc queries) | |||
When entering data into a range of cells, select the entire range. | Excel Discussion (Misc queries) | |||
Select Using Range | Excel Discussion (Misc queries) | |||
I cannot select a range | Excel Discussion (Misc queries) | |||
Select Sheet then Select Range | Excel Programming |