Macros and deleting a column

I've got a "contents" sheet which has numerous buttons which take users to a 
cell on a separate worksheet.

I'd like to delete some of the columns in the worksheet as they are no 
longer needed. When I do this however the macro doesn't amend to the new cell 
reference. 

There's a lot of coulmns to delete and a lot of macros that won't work.

Any ideas gratefully received.
0
blackcat (24)
11/12/2004 4:54:59 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
360 Views

Similar Articles

[PageSpeed] 50

Hi
you may post the relevant part of your macro

--
Regards
Frank Kabel
Frankfurt, Germany

"Blackcat" <Blackcat@discussions.microsoft.com> schrieb im Newsbeitrag
news:F757F808-C3D5-484F-B46F-4F1D6A984D50@microsoft.com...
> I've got a "contents" sheet which has numerous buttons which take
users to a
> cell on a separate worksheet.
>
> I'd like to delete some of the columns in the worksheet as they are
no
> longer needed. When I do this however the macro doesn't amend to the
new cell
> reference.
>
> There's a lot of coulmns to delete and a lot of macros that won't
work.
>
> Any ideas gratefully received.

0
frank.kabel (11126)
11/12/2004 5:11:24 PM
Is there anything to see on the buttons or are they just for links?

I am guessing you just had them for links, and  would probably do
much better without buttons and use
hyperlinks in the cells  either object hyperlinks (ctrl+k) or
   HYPERLINK Worksheet Formulas
or with an event macro when you select a cell to let the
macro figure out where you want to go if you can determine
that such as based on the cell in column A of the row.

Some coding that might be of help depending on what you have
and what you need to do.

Extract and Place Hyperlink from Shape into cell to the right
http://www.mvps.org/dmcritchie/excel/shapes.htm#ExtractLinkToRightOfShapes

Event Macro to figure out where to go  -- FollowHyperlink
allows you to use the browser shortcut keys back  and Forward.
   http://www.mvps.org/dmcritchie/excel/event.htm#ws_fh

Coding a link address and friendly name using HYPERLINK Worksheet function (#hyperlink)
     http://www.mvps.org/dmcritchie/excel/sheets.htm#hyperlink
The trick is to keep the cell addresses out of the double quotes so that
they will automatically adjust when you move columns around, rename worksheets, etc.

More information on hyperlinks   throughtout...
     http://www.mvps.org/dmcritchie/excel/buildtoc.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"Blackcat" <Blackcat@discussions.microsoft.com> wrote in message news:F757F808-C3D5-484F-B46F-4F1D6A984D50@microsoft.com...
> I've got a "contents" sheet which has numerous buttons which take users to a
> cell on a separate worksheet.
>
> I'd like to delete some of the columns in the worksheet as they are no
> longer needed. When I do this however the macro doesn't amend to the new cell
> reference.
>
> There's a lot of coulmns to delete and a lot of macros that won't work.
>
> Any ideas gratefully received.


0
dmcritchie (2586)
11/12/2004 6:32:27 PM
Thanks for the help. I think I'm sorted now.



"David McRitchie" wrote:

> Is there anything to see on the buttons or are they just for links?
> 
> I am guessing you just had them for links, and  would probably do
> much better without buttons and use
> hyperlinks in the cells  either object hyperlinks (ctrl+k) or
>    HYPERLINK Worksheet Formulas
> or with an event macro when you select a cell to let the
> macro figure out where you want to go if you can determine
> that such as based on the cell in column A of the row.
> 
> Some coding that might be of help depending on what you have
> and what you need to do.
> 
> Extract and Place Hyperlink from Shape into cell to the right
> http://www.mvps.org/dmcritchie/excel/shapes.htm#ExtractLinkToRightOfShapes
> 
> Event Macro to figure out where to go  -- FollowHyperlink
> allows you to use the browser shortcut keys back  and Forward.
>    http://www.mvps.org/dmcritchie/excel/event.htm#ws_fh
> 
> Coding a link address and friendly name using HYPERLINK Worksheet function (#hyperlink)
>      http://www.mvps.org/dmcritchie/excel/sheets.htm#hyperlink
> The trick is to keep the cell addresses out of the double quotes so that
> they will automatically adjust when you move columns around, rename worksheets, etc.
> 
> More information on hyperlinks   throughtout...
>      http://www.mvps.org/dmcritchie/excel/buildtoc.htm
> 
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
> My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm
> 
> "Blackcat" <Blackcat@discussions.microsoft.com> wrote in message news:F757F808-C3D5-484F-B46F-4F1D6A984D50@microsoft.com...
> > I've got a "contents" sheet which has numerous buttons which take users to a
> > cell on a separate worksheet.
> >
> > I'd like to delete some of the columns in the worksheet as they are no
> > longer needed. When I do this however the macro doesn't amend to the new cell
> > reference.
> >
> > There's a lot of coulmns to delete and a lot of macros that won't work.
> >
> > Any ideas gratefully received.
> 
> 
> 
0
blackcat (24)
11/15/2004 4:24:02 PM
Glad you're to have helped,  newsgroups are great.

Since I can't tell ahead of time what solution might work for you,  I try to
provide several possibilities. some related to each other.   Was there
any particular direction that you took to solve your problem.

Was I correct in assuming you could replace the buttons with links
with more conventional hyperlinks  or  an event macro.   Or did you
just use the information to correct the links within the button shapes.
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm

"Blackcat" <Blackcat@discussions.microsoft.com> wrote...
> Thanks for the help. I think I'm sorted now.


0
dmcritchie (2586)
11/15/2004 4:40:17 PM
Reply:

Similar Artilces:

Duplicate entries between two columns
Need some help with an excel formula. I have column A with 962 entries. I have column B with 898 entries. I want to find out the matched entries of the two columns in Column C. Thanks, Mazhar :rolleyes: -- mazalam ------------------------------------------------------------------------ mazalam's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26768 View this thread: http://www.excelforum.com/showthread.php?threadid=400897 Put this in C1: =isnumber(match(b1,a:a,0)) and drag down. True's will mean that B has a match somewhere in column A. mazalam wrot...

VBA/Row number/column numer
If I want to specify a cell in VBA but only know it's row NUMBer and not column number, how would I describe this cell in vba? i.e. instead of AA5, i want to be able to talkb about it as being column 27 and row 5... thanks! Mika, Cells(Row, Column) Cells(2, 3) would give you cell C2 -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "mika." <conceptualness@hotmail.com> wrote in message news:07ad01c37be7$ffd1b0a0$a101280a@phx.gbl... > If I want to specify a cell in VBA but only know it's row > NUMBer a...

Deleting Old Email Address
In Outlook when I change an email address in Contact, the old one is not deleted and appears when I try to correspond. I can not find the hidden address book in which the old email address resides in order to remove or how to set up a preference to permanently remove the old address. If this is Outlook 2002 or later, start typing the address into the To box and when the bad address pops up, highlight it and press Delete. -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. ...

Emails sent from users that have been deleted???
I thought we closed the open relay on our exchange server 2000 but we still get system administrator undeliverable messages on a regular basis. They will send us spam using old accounts that have been deleted and sometimes they will actually send them from active users so it looks like an employee is sending us spam. Can someone please help me stop this spoofing before I take a baseball bat to my exchange server? We also get about 100 - 200 spam emails daily on some accounts so how can I stop this do I have to upgrade to exchange 2003? Are you talking about backscatter? You're...

stacked column with series in variable locations
I don't think this can be done within Excel, but then again, some of you have some very creative ways of getting things done! I have a client who would like to create a stacked column chart. Here is some sample data. Day 1 Day 2 Day 3 ab 6:00:00 AM 6:00:00 AM cd 6:30:00 AM 2:50:00 PM ef 9:15:00 AM 6:00:00 AM gh 1:30:00 PM hi 4:00:00 PM 6:45:00 AM 1:00:00 PM The chart would have the Days along the X axis and the times along the Y. Each day goes from 6:00 am - 6:00 pm. On Day 1, Product ab is run from 6:00-6:30, Then cd from 6:30-9...

Windows Mail- Delete it from Server
I have been using "Delete it from Server" option in Windows Mail Rules successfully for a number of years now until recently. The I noticed i began receiving mails that I have defined to be deleted from server. To further this, I used another email address defining the emails from this address to be deleted from server but the emails still come through. Can anyone enlighten and help me on this? I am using Vista Home Basic and updated to the most current. The mail program is the Windows Mail. "rbernal43" <rbernal43@discussions.microsoft.com> wrote in m...

Delegates and Deleted / Disabled Accounts
Greetings, I have a situation where a user has Delegates listed in Outlook, but when I look at the PublicDelegates attribute for the same user in AD there are none. One of the delegate accounts has been deleted from AD, the other account is disabled and is no longer mail enabled. Is there another attribute in AD which I should be looking at which accurately reflects which users have Delegate access to the mailbox. The problem resulting from all this is that when a meeting request is sent to such a user the sender gets back NDRs from each of the zombie delegates. Now I could just lo...

How to disable the usage of "Permanently Deleting Items"
Hello, I want to disable the usage of "Permanently Deleting Items" for every user. Is there a way to disable this on the Exchange server or with Outlook. Every object must go into the folder deleted items. This setup shall help for better recovery support. Thanks Frank ...

macro for bubble chart
When I record the following code through macro recorder, then the bubble chart comes out fine. However, when I then try to rerun the same code then I get an error message: Run-time error '1004' Method 'ChartType' of object '_Chart' failed Any advice on why the xlBubble as chart type is not working? Sub Macro2() Charts.Add ActiveChart.ChartType = xlBubble ActiveChart.SetSourceData Source:=Sheets ("Sheet2").Range("A2:C6"), PlotBy:= _ xlColumns ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2" End S...

How do I sort a column of formulas in Excel? #2
I have a column of dates that I produced by putting in the first date. Then the next and following were simply a formula =SUM(A4+7) and so on for five years of Sundays. I wanted to have two for each sunday I could have them sorted by AM or PM so I duplicated the list and put AM in the next column beside the first 5 years of Sundays and PM beside the next five years. I then sorted with column A (dates) descending and column B (AM/PM) descending. I expected to get 01/01/2000 AM and then 01/01/2000 PM under each other and so on but the formulated column would not sort. I tried a sample of ...

Seperating cells and columns
Is there a way to seperate some cells and/or columns so you could have different height cells/columns that are in the same row? thx, Dave -- DRLski ------------------------------------------------------------------------ DRLski's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26892 View this thread: http://www.excelforum.com/showthread.php?threadid=401166 inserts some rows above and below your initial row and merge the ones that need to be higher with cells above and/or below -- Dnereb -------------------------------------------------------------------...

Excel 2003 Macro File
Once I establish Macros in Excel 2003 can I easily transfer these to another computer running Excel 2003? At least in 2002, macros are part of the file. So, if you move the file they go too or you could copy/paste or export/import. -- Don Guillett SalesAid Software donaldb@281.com "Boyd Burnette" <anonymous@discussions.microsoft.com> wrote in message news:016e01c3cfd0$98af7a50$a301280a@phx.gbl... > Once I establish Macros in Excel 2003 can I easily > transfer these to another computer running Excel 2003? Boyd Macros reside in a file, either the file in which yo...

macro to move from directory to directory
I am writing/recording a macro to make changes in 64 different spreadsheets in 64 different folders on a network. I am just learning vba. I don't know how to tell Excel to move through the 64 folders, test for the spreadsheet to be sure it's there, then make changes. Any suggestions? TIA Dave I would use a worksheet to help out. In A2:A65, I'd put the 64 folders to check. In B1, I'd put the filename to look for. Then something like this: Option Explicit Sub testme2() Dim myRng As Range Dim myCell As Range Dim myFileName As String Dim TestStr As String ...

Recovering accidently deleted word .doc
Hi, I've made a bad mistake today by accidently deleting a folder that contained a .doc file I was working on for some days. Recycle Bin's empty and on top of it I missed making a backup ... I've already tried to recover it with several tools, but I would only find some images that where stored in the same folder. The doc's gone. I have tried Restoration Version 2.5.14 and the PCI Filerecovery trial. Is there a chance, that contents of the file are still there, but under a different name? (I have also tried searching for .asd and .tmp files without success.) Or does anybody ...

delete blocked senders messages on receipt why junk mail them?
Why is it that after going to all the trouble of putting people onto a blocked senders list the filter still puts their messages into the junk mail folder for you to delete them? Or am I understanding the training wrong? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane....

Macros not running
Ok, so here is the situation. There is this excell file that has macros in it. So normally when you open the file a message box pops up and asks you if you want to enable or disable the macros. In my case we want to enable them. The problem is that when I open this file that message box does not come up and it seems like it automatically defaults to "disable". This is a big problem as there are check boxes and radio buttons that must be selected....hence the reason this document was created. Does anyone have any ideas? Why is excel doing this...on one PC only. If yo...

Beginners Macros
A relative novice with Excel, I use a number of worksheets for recording amongst other things, when various items of plant and equipment are due for test and inspection. As an initial experiment (I need to expand it in due course to do more complicated filters) I have recorded a macro to filter out items due for test within given dates and print out the result using the AutoFilter function. Sample data below: DESCRIPTION DATE LAST SEEN Yale Lever Hoist 05/08/04 Yale Lever Hoist 05/08/04 Yale Lever Hoist 06/08/04 Winch Rope 03/02/05 The macro is as follows: Sub Sort() ' '...

Rule for permanent delete from a @domain.com
Hi. I have outlook 2003 SP1 and I have a rule to make permanent delete for incoming emails from @domain.com (for example). But not works!!!!, it delete all incoming emails and not only @domain.com. Somebody could help me please with the correct rule or other solution for my problem. Thanx in advance.! How does the rule read? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & ...

Delete part of each record
Hello, In a field of a table there are entries with address and city in the same record. For example: High street 55 Paris. I only want the address in that field, so I need the city in the end of each record to be cut off. I guess what I need is any word after the last number of the entry to be deleted. Not just any number of each record, but after the LAST number of the entry since there are streets that contain numbers as part of the street name for example October 25th street. Can someone please help. Thank you Dimitris hi Dimitris, On 09.04.2010 11:08, Dimitris ...

Deleting an Exchange 5.0 server from site
We have one exchange server 5.5 and one 5.0 in our site. We would like to retire the 5.0 server. Is there any procedure to delete this 5.0 server from the site? Thanks See http://support.microsoft.com/?id=152959 in case the 5.0 server holds any of the roles assigned to the first server installed into the site. This article contains a link to another article covering removing the server from the site. -- Neil Hobson Exchange MVP For Exchange news, links and tips, check: http://www.msexchangeblog.com "Malcolm" <anonymous@discussions.microsoft.com> wrote in message news:1...

Outlook 2003 Macro Menu disabled
We're having a problem in which the entire macro menu under Outlook 2003 is greyed out. I am the administrator of the network and just installed Small Business Server 2003, upgrading outlook to 2003 using the wizards. I'm assuming SBS took the liberty of setting a group policy, registry entry or some such to disable the menu. But I can't find the policy or any documentation. Any suggestions how to undo this lockdown? ...

Delete mail from Outbox
Hello all, I have a mail in my outbox (hotmail thru outlook 2003) and due to its large attachment size (13mb) it can't be send. The thing is everytime i open Outlook it always try to send this mail and delays all other things like downloading my mails or send my mails. I tried to delete this mail from outbox but it doesn't get deleted and always tries to send. Tried to delete clicking on the (X) and right-clicking and nothing happens. How do i delete this mail from my outbox?? It's getting kinda annoying. Thanks in advance, JR. Jaime Rodr�guez <jaime_mrod@hotmail.com> wr...

select columns by column index
Hi to all. I am having a small problem. I would like to select columns by their indexes. psedou script is as follows. select column(1), column(2) from table Is it possible to do ? and (Number) is going to be dynamic. Thanks in advance Best regards, Ozgur Tayfun __________ Information from ESET NOD32 Antivirus, version of virus signature database 4611 (20091116) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com It's usually considered good practice to avoid depending on the order of columns in a table. So I would reco...

How to insert macros?
Version: 2004 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I can't find out how to use Macros in Worrd 2004 for Mac. Help shows me the toolbar for creating a macro, but how do you insert one into the Word document? Let me clarify my term &quot;macro&quot;, in case I'm using the wrong terminology. I'm talking about large blocks of text that I use repeatedly in reports, so that I don't have to cut and paste from other documents. So, if I created a &quot;macro&quot; called &quot;xyz&quot; for a paragraph of text, and I wanted to insert ...

macro for 4 constraints in autofilter
Hi Not sure if this is possible, or if I have tackled this the right way so any help greatly appreciated! I am (trying to) write a macro that will use multiple constraints (in 1 column) in autofilter, this is so that I don't have to use the advanced filter option. The result of the macro should return rows that contain "19" or "20" or "act" or "regul" in the 3rd field, and hide the rows that don't. My macro that is not working at the moment: ' Sheets("Agr-Tra").Select Selection.AutoFilter Field:=3, Criteria1:="=r...