Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and the Yahoo Answers website is now in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.

"Anchoring" in Excel?

Ill try to explain this the best I can.

I have about 3 or 4 sheets, one main (SHEET1) sheet then the others (SHEET2-4). I currently have a hyperlink on the main sheet linking to all the others.

What I want to be able to do is when I click on the SHEET2 link on the main sheet I want it to go the the first unused row on that sheet. I know you can tell the link where to go (you can tell it to open A65 for example) but I need a way for excel to look for the first open/unused row. I cant just type in A65 in the hyperlink properties because in a day or two cell A65 will no longer be the first blank row.

Any help would be greatly appreciated

2 Answers

Relevance
  • 1 decade ago
    Favorite Answer

    Here's a macro that will select the first unused row whenever the sheet is selected (either by using the link or just when you select it with the sheet tab).

    To install the macro, right click on the sheet tab in the lower left and select View Code. Paste the macro in the VB edit window. You would have to do this for each sheet where you want to have the first unused row automatically selected.

    Private Sub Worksheet_Activate()

    Dim LastRow As Long

    ActiveSheet.UsedRange

    LastRow = Cells.SpecialCells(xlLastCell).Row + 1

    'Select the cell in column A of the 1st unused row

    Cells(LastRow, 1).Select

    'Select the entire 1st unused row

    'Cells(LastRow, 1).EntireRow.Select

    End Sub

    This macro selects the Cell in column A that is the first un-used row. If you want to select the entire row and not just the column A cell, uncomment the 2nd-to-last line in the code...

    Cells(LastRow, 1).EntireRow.Select

  • 1 decade ago

    FOLLOW THIS LINK FOR A NUMBER OF ANSWERS TO YOUR QUESTION. www.cpearson.com/ tHIS IS A GREAT WEBSITE TO FIND ANSWERS FOR EXCEL

    Source(s): I used to write programs in excel for the Kitchen Cabinet industry. I am a little rusty now, but the above website answered most of my programming questions.
Still have questions? Get your answers by asking now.