Select All Active Cells 2

[SpankYou]

Hey,

I have an Excel workbook, and on one sheet there is a drop down box, in this drop down box is a list of ID's as declared in the second worksheet. Then using the lookup function depending on the ID entered, the remaining cells in that row enter the data relevant to the ID in sheet 2. This is really all besides the point but might help you understand what I am trying to do.My problem is that to get the Vlookup function to work each time some data is added to the look up table, the ID must be reselected and Named, My ItemData must be reselected and named, and then the data must be arranged alphabetically. I thought that I could perform all these functions as a macro, but I'm a bit new to excel.

Basically I need to know either a function or some VBA code, that will allow me to select all active cells in a particular column starting from A2, and also a way to select all active cells on a page but missing the first row. If anyone knows how to do this or can give me a hint I'd be very grateful, even if its a case of you only know how to select all active cells in a column (disregard the miss A1 bit) that would be great.

Thanks for any help


Cheers


Sam

"You couldn't fool your mother on the foolingest day of your life if you had an electrified fooling machine." - Homer

 

[SpankYou]

Hey,

Sorry when I said all active cells, I mean all cells with data in them.

Cheers

Sam

"You couldn't fool your mother on the foolingest day of your life if you had an electrified fooling machine." - Homer

 

[Loomah]

Hi
Assuming the list is contiguous then

Range(Cells(2, 1), Cells(2, 1).End(xlDown)).Select

will give you the cells in column A that you want.

I'm not sure what you mean about selecting all active cells (technically there can only be one active cell!) but assuming you require all cells containing data then check out the FAQ section for info on used range and finding the last cell (there are at least 2 FAQs on this subject)

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Loomah]

OK
Your second post wasn't there when I posted!! You might also have a look at "SpecialCells" in help as this may give you what you want (but I don't think it will apply here!)
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[JohnAcc]

If there are non contiguous cells then use:

Dim myrng As Range
Set myrng = Range("B2", Range("B65536&quot.End(xlUp))

Rgds, John

 

[SpankYou]

Hey,

Well I've got the code working thanks to your FAQ Loomah but I have a slight problem. When I create A macro I run the macro to select the ID range and save the name as ID, and then run the code to select the ItemData range and save the Name as ItemData. Now when i create the Macro and run it without entering any data it works fine. But when I add some data, the macro I have just created still selects the old selection, although when I test the code I used to select the ItemData and ID areas, they work fine. the macro seems to just use the original selections when the macro was recorded rather than executing the code. Any ideas why this may be happening?

The macros that I call are as follows (I recorded sections of the macro Individually to see if this would make a difference),

Sub MacroDeleteList()
'
' MacroDeleteList Macro
' Macro recorded 25/09/2003 by Sam Sheppard
'

'
ActiveWorkbook.Names("ID&quot.Delete
ActiveWorkbook.Names("ItemData&quot.Delete
End Sub
Sub MacroSaveID()
'
' MacroSaveID Macro
' Macro recorded 25/09/2003 by Sam Sheppard
'

'
Application.Run "MainCableScheduleTemplate.xlt!FindColumn"
ActiveWorkbook.Names.Add Name:="ID", RefersToR1C1:="=Sheet2!R5C1:R29C1"
End Sub
Sub MacroSaveItemData()
'
' MacroSaveItemData Macro
' Macro recorded 25/09/2003 by Sam Sheppard
'

'
Application.Run "MainCableScheduleTemplate.xlt!FindUsedRange"
ActiveWorkbook.Names.Add Name:="ItemData", RefersToR1C1:= _
"=Sheet2!R5C1:R29C11"
End Sub
Sub MacroSort()
'
' MacroSort Macro
' Macro recorded 25/09/2003 by Sam Sheppard
'

'
Application.Run "MainCableScheduleTemplate.xlt!FindUsedRange"
Selection.Sort Key1:=Range("A5&quot, Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub


Thanks For Any Help

Cheers

Sam


"You couldn't fool your mother on the foolingest day of your life if you had an electrified fooling machine." - Homer

 

[SpankYou]

Ok after looking at the code I realise my mistake, the macro saves the selection as the selection to use each time, is there any way in which I can say

RefersToR1C1:= CurrentSelection

Thanks Sam

"You couldn't fool your mother on the foolingest day of your life if you had an electrified fooling machine." - Homer

 

[xlbo]

try this:

RefersTo:= CurrentSelection.address

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[xlbo]

whoops:

RefersTo:= Selection.address

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[SpankYou]

Hey xlbo,

I have run that code but it doesn't quite work, It does add the title to the Name List now but as a reference it is putting...

="$A$5:$A$32"

instead of...

=Sheet2!$A$5:$A$32

and the Names do not appear in the drop down box.

Basically the code is identical to the above apart from I've replaced ...

RefersToR1C1:="=Sheet2!R5C1:R29C1"

with...

RefersTo:= Selection.address

Any Ideas,

Cheers

Sam

"You couldn't fool your mother on the foolingest day of your life if you had an electrified fooling machine." - Homer

 

[xlbo]

try this then
RefersTo:= activesheet.name & "!" & Selection.address

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[SpankYou]

Hey xlbo,


Ok one step away, it now equals...

="Sheet2!$A$5:$K$32"

and basically it's right but needs to be without the quotation marks...

=Sheet2!$A$5:$K$32

I've tried the replace function but How do you replace a "?

thanks for all your help,

Cheers

Sam



"You couldn't fool your mother on the foolingest day of your life if you had an electrified fooling machine." - Homer

 

[xlbo]

once more unto the breach.....
RefersTo:= "=" & activesheet.name & "!" & Selection.address

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[SpankYou]

Thankyou Thankyou Thankyou,

I feel like loging in under many names and sending you many stars, until my computer speeds up you'll have to settle with this one.

Thanks very much xlbo you are a genius!!!

Cheers

Sam

"You couldn't fool your mother on the foolingest day of your life if you had an electrified fooling machine." - Homer