You are here:

Excel/Help with macro to clear data from rows & columns

Advertisement


Question
Hi Jan
Below is a link to a sanitised workbook (you may recognise from the past) to demonstrate what I have and what I am trying to do.
http://1drv.ms/1Et6I2q
As can be seen on sheet Crew Names, COLUMN A contains the names of the crew as hyperlinks.  When a hyperlink is selected it transfers to the corresponding name in ROW 2 of the Crew Shares sheet, and similarly if a hyperlink on the Crew Shares sheet is clicked on, it will transfer back to the corresponding name in the Crew Names sheet, this all works fine.
When a crew member leaves, (after a period of time) I need to remove them and all their data from both sheets.   I have tried to delete the row on the Crew Names sheet and the column on the Crew Share sheet but I then loose the referential integrity with the hyperlinks, the hyperlinks no longer correspond to the correct cells on the other worksheet.
Is it possible to have a button on the Crew Names sheet named “Permanently Remove Crewman”, when pressed a listbox/combobox appears with ALL crew names showing.  Choosing a name from the list should clear data from cells C, D, E & G to the end of the row on the Crew Names sheet for the row corresponding to the name selected, it should also clear the data in row 3 down to but not including the Totals cell of the corresponding column on the “Crew Shares sheet”

Any help / comments would be greatly appreciated,
Keith
PS
Please bear in mind that most of the existing code in the workbook has been created by gaining snippets and modifying to suit or by people like yourself helping as my knowledge of vba is very limited.

Answer
Hi Keith,

Hyperlinks are not treated like cell formulas so removing rows or columns will not update them.

For the crew Shares there is a simple solution: use the hyperlink worksheet function. The one for Kenny Thomas would become:

=HYPERLINK('Crew Names'!$A5,'Crew Names'!$A5)

But for Crew Names this is a bit harder, as you cannot simply replace the names with a formula.

One way would be the have a separate names list somewhere and pull the names from there.

So I copied all names onto a newly inserted worksheet called Names and used this formula on Crew Names:

=HYPERLINK(OFFSET('Crew Shares'!$G$1,0,ROW()-4,1,1),Names!A1)
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jan Karel Pieterse

Expertise

Excel and Excel/VBA questions

Experience

Excel MVP

Organizations
Self employed Excel developer

Education/Credentials
Bachelor in Chemical Engineering

Awards and Honors
Microsoft MVP award since 2002

Past/Present Clients
Shell, Fortis bank, ABN-AMRO bank, Morgan Stanley, ...

©2016 About.com. All rights reserved.