You are here:

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


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.
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,
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.

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


All Answers

Answers by Expert:

Ask Experts


Jan Karel Pieterse


Excel and Excel/VBA questions


Excel MVP

Self employed Excel developer

Bachelor in Chemical Engineering

Awards and Honors
Microsoft MVP award since 2002

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

©2016 All rights reserved.