VB coding to clear a selection?

I currently have the selection clear by changing the cursor location
to cell D2 (Range("D2").Select).  So if the entire sheet had all the
cells selected, this would clear the selection but would also move to
another location.  I actually need the active cell to remain the same
as before the macro was invoked, but don't know the VB code for this.

Thanks for any help in advance.

0
StargateFan
1/29/2005 3:05:46 AM
excel 39880 articles. 2 followers. Follow

8 Replies
520 Views

Similar Articles

[PageSpeed] 2

Maybe.............

    Range("A1:A5").Select
    Selection.ClearContents


Vaya con Dios,
Chuck, CABGx3

"StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
news:68vlv05408hjspknpjvm88sgd3ut775vhr@4ax.com...
> I currently have the selection clear by changing the cursor location
> to cell D2 (Range("D2").Select).  So if the entire sheet had all the
> cells selected, this would clear the selection but would also move to
> another location.  I actually need the active cell to remain the same
> as before the macro was invoked, but don't know the VB code for this.
>
> Thanks for any help in advance.
>


0
croberts (1377)
1/29/2005 3:29:54 AM
I think Chuck would mean

    Range("A1:A5").ClearContents

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"CLR" <croberts@tampabay.rr.com> wrote in message
news:%23pd6bJbBFHA.2016@TK2MSFTNGP15.phx.gbl...
> Maybe.............
>
>     Range("A1:A5").Select
>     Selection.ClearContents
>
>
> Vaya con Dios,
> Chuck, CABGx3
>
> "StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
> news:68vlv05408hjspknpjvm88sgd3ut775vhr@4ax.com...
> > I currently have the selection clear by changing the cursor location
> > to cell D2 (Range("D2").Select).  So if the entire sheet had all the
> > cells selected, this would clear the selection but would also move to
> > another location.  I actually need the active cell to remain the same
> > as before the macro was invoked, but don't know the VB code for this.
> >
> > Thanks for any help in advance.
> >
>
>


0
bob.phillips1 (6510)
1/29/2005 9:11:55 AM
See also
http://www.dicks-blog.com/archives/2004/04/27/beginning-vba-select-and-activate/
for some theory.

HTH. Best wishes Harald


"StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> skrev i melding
news:68vlv05408hjspknpjvm88sgd3ut775vhr@4ax.com...
> I currently have the selection clear by changing the cursor location
> to cell D2 (Range("D2").Select).  So if the entire sheet had all the
> cells selected, this would clear the selection but would also move to
> another location.  I actually need the active cell to remain the same
> as before the macro was invoked, but don't know the VB code for this.
>
> Thanks for any help in advance.
>


0
innocent (844)
1/29/2005 11:27:20 AM
hi stargatefan
do you want clear the contents or just get rid of the selected
cells(i.e.deactivating them) & again activate the cell on which you was
working before.

you can do this by using a temporary range which you can declare equal
to your active cell(s) then run the macro & then again activating this
temporary range.

Regards
NC

0
1/29/2005 12:26:12 PM
On Sat, 29 Jan 2005 09:11:55 -0000, "Bob Phillips"
<bob.phillips@notheretiscali.co.uk> wrote:

>I think Chuck would mean
>
>    Range("A1:A5").ClearContents

YIIKES!  <g>  That deleted the contents as well as clearing the
selection!  Any way to _just_ de-select (clear the selection) while
keeping the "cursor" in the same cell as before invoking the macro?

Thanks much!

>-- 
>
>HTH
>
>RP
>(remove nothere from the email address if mailing direct)
>
>
>"CLR" <croberts@tampabay.rr.com> wrote in message
>news:%23pd6bJbBFHA.2016@TK2MSFTNGP15.phx.gbl...
>> Maybe.............
>>
>>     Range("A1:A5").Select
>>     Selection.ClearContents
>>
>>
>> Vaya con Dios,
>> Chuck, CABGx3
>>
>> "StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
>> news:68vlv05408hjspknpjvm88sgd3ut775vhr@4ax.com...
>> > I currently have the selection clear by changing the cursor location
>> > to cell D2 (Range("D2").Select).  So if the entire sheet had all the
>> > cells selected, this would clear the selection but would also move to
>> > another location.  I actually need the active cell to remain the same
>> > as before the macro was invoked, but don't know the VB code for this.
>> >
>> > Thanks for any help in advance.
>> >
>>
>>
>

0
StargateFan
1/29/2005 1:30:49 PM
On Sat, 29 Jan 2005 12:27:20 +0100, "Harald Staff"
<innocent@enron.invalid> wrote:

>See also
>http://www.dicks-blog.com/archives/2004/04/27/beginning-vba-select-and-activate/
>for some theory.
>
>HTH. Best wishes Harald

Very interesting, brings up the question on whether my
keystrokes-recorded macro would work, then, without selecting the
_entire_ worksheet.  Seems like it would, but thought I'd ask.  I
invoke this toolbar macro after copy/pasting a bunch of data into
Excel.  Since invoking with full attributes means that the data
structure remains intact (very important), I created the macro to
quickly and easily re-format the pasted in text to what the rest of
the sheet is for easier editing and for a proper final look.

But as I'm working on different rows, the problem has been that the
way I've been clearing the selection itself is to just hit Ctrl+Home
which takes me to the top cell in the sheet.  Not sure that this
situation falls under the troubles the website above talks about as
the _entire_ worksheet is selected.

Thanks!

>"StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> skrev i melding
>news:68vlv05408hjspknpjvm88sgd3ut775vhr@4ax.com...
>> I currently have the selection clear by changing the cursor location
>> to cell D2 (Range("D2").Select).  So if the entire sheet had all the
>> cells selected, this would clear the selection but would also move to
>> another location.  I actually need the active cell to remain the same
>> as before the macro was invoked, but don't know the VB code for this.
>>
>> Thanks for any help in advance.
>>
>

0
StargateFan
1/29/2005 1:35:16 PM
On 29 Jan 2005 04:26:12 -0800, "NC" <nnchoudhari@yahoo.com> wrote:

>hi stargatefan
>do you want clear the contents or just get rid of the selected
>cells(i.e.deactivating them) & again activate the cell on which you was
>working before.

Ah, the problem was in the terminology.  I'm actually just
de-activating the selection again.

>you can do this by using a temporary range which you can declare equal
>to your active cell(s) then run the macro & then again activating this
>temporary range.

Lordy, that's beyond my limited VB knowledge! <g>  Ah well, I'll just
keep working with what I have, I guess, unless/until something else
comes along.

Thanks much!

>Regards
>NC

0
StargateFan
1/29/2005 1:36:29 PM
ActiveCell.Select

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03

----------------------------------------------------------------------------
                  It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
news:9u3nv0lb004mhig0fo34702gt89rkuh0on@4ax.com...
> On Sat, 29 Jan 2005 09:11:55 -0000, "Bob Phillips"
> <bob.phillips@notheretiscali.co.uk> wrote:
>
> >I think Chuck would mean
> >
> >    Range("A1:A5").ClearContents
>
> YIIKES!  <g>  That deleted the contents as well as clearing the
> selection!  Any way to _just_ de-select (clear the selection) while
> keeping the "cursor" in the same cell as before invoking the macro?
>
> Thanks much!
>
> >-- 
> >
> >HTH
> >
> >RP
> >(remove nothere from the email address if mailing direct)
> >
> >
> >"CLR" <croberts@tampabay.rr.com> wrote in message
> >news:%23pd6bJbBFHA.2016@TK2MSFTNGP15.phx.gbl...
> >> Maybe.............
> >>
> >>     Range("A1:A5").Select
> >>     Selection.ClearContents
> >>
> >>
> >> Vaya con Dios,
> >> Chuck, CABGx3
> >>
> >> "StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
> >> news:68vlv05408hjspknpjvm88sgd3ut775vhr@4ax.com...
> >> > I currently have the selection clear by changing the cursor location
> >> > to cell D2 (Range("D2").Select).  So if the entire sheet had all the
> >> > cells selected, this would clear the selection but would also move to
> >> > another location.  I actually need the active cell to remain the same
> >> > as before the macro was invoked, but don't know the VB code for this.
> >> >
> >> > Thanks for any help in advance.
> >> >
> >>
> >>
> >
>


0
ken.wright (2489)
1/29/2005 2:33:55 PM
Reply:

Similar Artilces:

Mutual fund codes
I have a mutual fund that I cannot get the prices for. The code is CA:AIM3593 and Money does not list that fund. I'm upgrading from Quicken 2002, which could download prices for this fund and I'm deciding between the latest versions of Money and Quicken. There are 7 other funds that I cannot get prices for because Money does not list the fund. The codes are all available at https://www.cannex.com/canada/english/fund/fp030ae.html and all work with Quicken. -- If you can keep your head about you, while all those around you are losing theirs, you just don't understand the...

How do I change a word's style and not select the whole word?
In PowerPoint, I can change a word's style (to bold, italics, or underline) simply by clicking somewhere in the word and then clicking on the style I want. However, in Word, I have to highlight the entire word in order to change the style. Is there a way to click in the word and change the style without having to select the whole word first? Tools > Options > Edit (Word 2007 - Word Options > Advanced > Editing Options) - check 'When selecting automatically select entire word'. -- <>>< ><<> ><<> <>>< >&l...

VB distmon?
Hi I have recently installed Office 2003. A couple of wired things happen in Excel: > Everytime I close the program I get a MS VB error that reads "Compile error in hidden module: Distmon" Every time open the program I get the same error, but the macro it is calling is "autoexecnew" > Since my new installation, I type in an simple function like TRIM and it tells me cannot find project library. any ideas? Thnaks! Alan Alan http://support.microsoft.com/default.aspx?scid=kb;EN-US;q307410 OFF: "Compile Error in Hidden Module" Error Message When You Sta...

Duplicate detection (looking for code)
I would like to add code to detect for duplicates or records with similar information. Looking to do this check on (firstname and lastname) and on address (street1, street2, city, state, zip) But I want something a little more advanced than just checking for exact matches. Wondering if anyone has some code they would care to share that might make my job of writing it a little easier? Example: Bob Smith and Bobby Smith would be detected as duplicates Rob Jones and Robert Jones would be detected as duplicates 123 main street pittsburgh, pa 15126 123 main st pittsburgh pa 15...

Bar code scanners
Hello All! I have a warehouse facility and several stores and am trying to find a way to use a barcode scanner with internal memory to scan large orders (200 pieces or greater) that i can then hook to my pc with RMS and "dump" the information into either a transfer out or an invoice. has anyone done this? if not does anyone have any recommendations? is there a person i could contact that may be able to write a short program or executable to make this happen? I would appreciate any and all information anyone may be able to provide. Thanks, Jim Hi Jim, I don't kno...

Multiple Charts; How to Select Colors?
I have a worksheet that contains several pie charts. I would like the color scheme of the pie slices to be different on each of the charts. How do I change the color format on each pie chart so that the color is individualized? Thanks Bill Falzone Hi, Color each segment manually on each of the charts. To color an individual slice click the pie series and then single click a slice. When one segment is selected double-click it and change it color or float the color palette and click the color of your choice. Repeat for each slice on each chart. If you are using 2007 the idea is th...

Colorising cells from code
I am using the code below which runs whenever a cell is edited -I think. I would like to change this code so that it can be 'run' against a range of cells rather than as Worksheet_Change code. What do I need to do to do that ? Thanks Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Range("A1").Value = "" Then If Not Intersect(Target, Range("B4:J34, B35:B39")) Is Nothing Then Select Case Target Case Sheet3.Range("A4") icolor = 34 Case Sheet3.Range("A5") icolo...

EAN 14, Dun 14
Hi, I have a printer TLP 2844 (Zebra) and I need to print bar codes using EAN 14 or Dun 14. Anyone have any idea how I can do that? I want just a simple sample in how do that. I posted this question here in excel forum because there isn't any group related as bar codes and because I want to work with that using just Excel. Thanks in advance, Magno magno_jr@terra.com.br You will need to buy a software designed for barcode like Lableview. EAN14 is not a common barcode so make sure it will do that. If you call Zebra, I think they sell something, if not they can recommand on...

Code does not work on merged cells
The following code works fine on single cells but I also have Dat Validation which for cosmetic purposes merges to the next cell e.g Cell B3 merges into cell C3 - why, if I use either B3 or B3:C3 in th code below does it not work? i.e. I should get an error message if try to delete data from one of the cells. Thanks _Code_ Application.EnableEvents = False If (Len(Range("b10")) = 0) Or (Len(Range("c10")) = 0) O (Len(Range("f10")) = 0) Then With Target If .Value = "" Then Application.EnableEvents = False .Value = "Invalid" MsgBox "You ...

CRichEditCtrl + selection(shift + end)
hi in my application i am doing syntax highlighting. currently i am able to color the keywords. but there is one problem. if i select this portion of keyword (which is in blue color) with keyboard (shift + End) then the color get's vanished.in contrast if i select the same text with mouse then it does not disturb. why something like this is happening? is the selection with keyboard and mouse are different in functionaliy? If anybody know's please suggest some solution. CRichEditCtrl creation code is like this m_rich.Create(ES_AUTOVSCROLL |ES_AUTOHSCROLL | ES_NOHIDESEL | ES_MULTILI...

Excel VB
I have two cells in a workbook that are dates. The cell Start_Date has a valid date in it. The second cell Test_Date is either blank or has a valid date in it. The IsDate test on both cells works fine, however, on a blank cell the Month function extracts "12". Anyone know why? I had to put the month function within a test for IsDate to make it work. Seems like Month function on a blank cell should return something other than a 12. Dave Test code: If IsDate(Range("Start_Date")) Then 'Cell has 11/01/09 in it. MsgBox ("start date a d...

Cannot Add any window to VB
Hello: I'm using a VM with GP 10.0 SP3 installed. In order to have the same as our production databases i copy the GP folder, entirely, which i think it was a mistake now I'm getting a "This cannot be added to Visual Basic", whenever i try to add a window, any window, to VB. Also it goes without saying that there's a lot of load errors starting GP. For the record the copy/paste thing worked, sort of, and before you start thinking that i was insane to have done this, there's a reason behind it, There's been a lot of tweaks to our production server, by our p...

How do I clear Email Delay Delivery Option?
Under Options; My Delay Deliver Box is checked, I uncheck the box and hit close. The email still delays. I reopen Options; and the Delay Delivery Box is check again and defaults to 5:00PM of the current day. Round and round we go; am i missing something here? "RAD" <RAD@discussions.microsoft.com> wrote in message news:D2867398-8FAD-4D67-ADAF-0771F059041E@microsoft.com... > Under Options; My Delay Deliver Box is checked, I uncheck the box and hit > close. The email still delays. I reopen Options; and the Delay Delivery Box > is check again and default...

vb from VC++
Hi, is it possible, and if so, to call a Vb macro from VC++. Or manipulate spreadsheets (ie draw graphs from the data within the workbooks) usinbg MFC functions cheers Anthony Presumably you are referring to MS Excel spreadsheets and workbooks. The easiest method would be to import the appropriate type library (.tlb) provided with Excel, having the class wizard generate the classes, which you then use to access the Excel Automation model (that is the same model that Excel VBA uses). Some investigation may be needed to determine which tlb to use, and to make sure you have a basic understand...

Sheet selection on Autoclose
Hi all, i was wondering whether if anyone can help me on this problem. I have this macro that protects the spreadsheet at worksheet and workbook level when i close spreadsheet, however, i discovered a problem when i have mutiple spreadsheets opened together with this particular sheet and exiting excel all together at once. The macro instead of targeting and running the macro for the worksheet it opened with, will just work for top-most opened spreadsheet which i am closing from. I do not want to hardcode the name of the spreadsheet into the macro as this is a generic macros t...

Auto assigning department Codes to a Project
We have many projects set up - within each project we assign multiple resources. The resources are often from different departments. Today, we set up a project for each department as we cannot seem to make PA "dynamically" assign our department value based upon the person assigned to the project. Is it possible to have a project dynamicaly assign a department code (a GL segment) based upon the person assigned to the project? -- Dave Not without customization using VBA or Dexterity. -- Charles Allen, MVP "David Schmidtknecht" wrote: > We have many project...

VB in Excel
In Excel I go into Visual Basic Editor, then Visual Basic Help, but when I type 'Passwords' for example it will bring up a list of related items, but some of them you can't select. Does this mean the installation CD is corrupt and hasn't installed all the components? I have reinstalled and repaired the installtion but to no avail. Any ideas anyone? Help! Thanks ...

Cntrl find
I have a large excel worksheet - zip codes, cities, states, area codes ... I need to be able to sort as well as find. Problem comes in with states that have zips starting with zero. I can get them to appear correctly as custom or special, but not able to do cntrl-find and bring up ... I can't believe I can't figure this out ... Format the Zip code column as TEXT and enter the leading zero normally as any other character.........then FIND will "find" it........... Vaya con Dios, Chuck, CABGx3 "jjjJackieCalifornia" <jjjJackieCalifornia@discussions.microsoft...

How do I clear a form without a button
Hi there I have the following cmdOK button for a form. Private Sub cmdOK_Click() Sheets("Band Members").Select Range("Last_Name").Select Selection.EntireRow.Insert ActiveCell.Value = txtName.Text ActiveCell.Offset(0, 1).Value = txtAddress.Text ActiveCell.Offset(0, 2).Value = txtSuburb.Text ActiveCell.Offset(0, 3).Value = txtPhone.Text ActiveCell.Offset(0, 4).Value = cboType.Text ActiveCell.Offset(0, 5).Value = "A" ActiveCell.Offset(0, 6).Formula = "=IF(Status = ""A"", VLOOKUP(Type,Fees_table,2),...

I cannot select a range
I wrote a macro, inter alia containing the following code: Const constName = "JCMail.xls" Const constPre = "JC" Private Sub CommandButton1_Click() ' Application.ScreenUpdating = False ' Gets and Inserts job card number Range("AB3").Select ' CommandButton1.Deactivate varRef = ActiveCell.Value copier End Sub Private Sub copier() ' Copies and pastes date Windows(constName).Activate Range("G3").Select When the macro reaches the last line of code (Range("G3").Select), I get the following error messa...

Opening a .doc from code
I am trying to open a Word document from an Access program using the following code: objWord.Documents.Open path\FileName objWord.Documents(1).Activate I can't get the Word document open on my desktop. What am I doing wrong? Thanks, Stanley You should show us all of the code so that we can see what you have set objWord to. Assuming that you have used something like Set objWord = GetObject(, "Word.Application") or Set objWord = CreateObject("Word.Application") then having declared docWord as a Word.Document, try Set docWord = objWord.Docum...

VBA: Insert Page Breaks code is not working in Excel 2010
I have a snippet of VBA code to insert page breaks after a particular row. This works fine in Excel 2007, but it doesn't in another Excel 2010 machine. It doesn't seem right to me that VBA code would behave differently in different versions of Excel. Am I missing something?? RE: Am I missing something?? Yes, your code. -- Regards Dave Hawley www.ozgrid.com "iswarya" <u59539@uwe> wrote in message news:a6e77011b51d5@uwe... >I have a snippet of VBA code to insert page breaks after a particular row. > This works fine in Excel 2007, but it doesn...

How do I retrieve my ID code.I have already registered.
I am trying to put my ID number into my product number area but after registering no code is coming up. What do I do? On Sat, 20 Feb 2010 19:16:01 -0800, Katherine <Katherine@discussions.microsoft.com> wrote: >I am trying to put my ID number into my product number area but after >registering no code is coming up. What do I do? Ummmmm... What "product number area"? What software are you talking about? You're asking in a technical support forum for Microsoft Access database developers. Nobody here can possibly tell what software you're trying to ...

Selecting across worksheets
How do I select across worksheets within a file? I want to select across worksheets and view the accumulating total at the bottom of the worksheet. =SUM(Sheet1!:Sheet3!A3) -- HTH RP (remove nothere from the email address if mailing direct) "JMS" <JMS@discussions.microsoft.com> wrote in message news:15A369D1-561E-4616-B0EB-0FA36A703566@microsoft.com... > How do I select across worksheets within a file? > > I want to select across worksheets and view the accumulating total at the > bottom of the worksheet. ...

VB.Net program to create XML file
I need to create an xml file from a VB program using fields returned from an sql query. I have a template that was provided for me and need to use verbatim. Since this is just a text file, I gather that I can just write out the file with all of the tags, etc., and name it .xml. Assuming that is correct what I might want to do is create a template so that if this file changes the names of the elements, all I have to do is change the template and if more elements get added I can then add them. Is there a tool that can do this for me? If I have to create one, any suggestions as to what I ...