Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jan 2002
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Saving keystrokes (Excel 2000)

    Good day all,
    I am working in Excel 2000 and have a sheet that I have to add entries in certain columns each one five rows apart. Is there a way to automate this process into one keystroke instead of five every time?
    Thanks in advance for any help.
    Glenn

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Saving keystrokes (Excel 2000)

    Do you mean the movement between columns? Would this help if attached to a button?

    Sub HideMyColumns()
    ThisWorkbook.ActiveSheet.Range("B:E,G:J,M:P,R:U"). EntireColumn.Hidden = True
    End Sub

    Easiest way to unhide the columns is click on the top right blank spot of the Worksheet to select all cells, right click and Column, Click Unhide.

    You can get very sophisticated with data entry techniques, what are you looking for?
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Lounger
    Join Date
    Jan 2002
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving keystrokes (Excel 2000)

    Actually what I was looking for was going from say A1 to A6 to A11, etc then I have to start at the top again and go from C1 to C6 to C11, etc. The way we receive our data this is a much quicker way to enter it than going across each row.
    I appreciate your response.
    Glenn

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Saving keystrokes (Excel 2000)

    Is the Workbook always laid out this way? If so you can protect all the cells EXCEPT the entry cells, and protect the worksheet; then you just tab between applicable cells, although this runs row-wise, and I don't see how to make it column-wise.

    <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> Thought about restructuring your data entry area?

    Or continuing on my earlier approach (perhaps pointlessly):

    Sub EntryAreaShowToggle()
    Dim blHideSetTo As Boolean
    If Not ThisWorkbook.ActiveSheet.Range("2:5").EntireRow.Hi dden Then
    blHideSetTo = True
    End If
    With ThisWorkbook.ActiveSheet
    .Range("2:5,7:10,12:15,17:20").EntireRow.Hidden = blHideSetTo
    .Range("B:B,D,F:F").EntireColumn.Hidden = blHideSetTo
    End With
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Saving keystrokes (Excel 2000)

    Hi Glenn,
    I agree with John that the easiest way to accomplish this is simply to hide the data you don't want - i.e. all the rows in between. This should do what you want:
    <pre>Sub fifthrowvis()
    Dim lngRow As Long, lngRowMultiple As Long, n As Long
    Application.ScreenUpdating = False
    lngRow = Selection.Row
    With ActiveSheet
    lngRowMultiple = Int(.UsedRange.Rows.Count / 5)
    .Range(.Cells(lngRow, 1), .UsedRange.SpecialCells(xlLastCell)).Rows.Hidden = True
    For n = 0 To lngRowMultiple
    .Rows(n * 5 + lngRow).Hidden = False
    Next n
    End With
    Application.ScreenUpdating = True
    End Sub

    Sub ShowAllRows()
    ActiveSheet.UsedRange.Rows.Hidden = False
    End Sub

    </pre>

    You will need to select the first row you want left visible before running the fifthrowvis macro, then run the ShowAllRows macro when you've finished.
    Hope that helps
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Saving keystrokes (Excel 2000)

    You could use the Worksheet_Change event to help. Right click on the sheet tab of the relevant sheet and enter the folloowing code. This will cause the selection to advance 5 rows after a cell value changes. It incorporates a switch so that if a given cell (A1 in this example) has a value orther that blank or zero the feature is enabled. You can remove the code in red if you do not want this, or change A1 to a suitable unused cell reference.

    Private Sub Worksheet_Change(ByVal Target As Range)
    <font color=red>If [A1] Then</font color=red> Target.Cells(1).Offset(5, 0).Select
    End Sub

    Andrew C

  7. #7
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Memphis, Tennessee, USA
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving keystrokes (Excel 2000)

    Glenn, I have worksheets similiar in structure to what you describe. I use a macro that moves me 5 cells down from where ever I am. I can then start over in another cell and go 5 cells from there, and so on. But I also need the conventional ENTER function to drop one cell down. I use a simple macro like this...

    Sub MoveFiveDown()
    ActiveCell.Offset(5, 0).Select
    End Sub

    You can then make button for it or map the macro to a key like Ctrl+q. Anyway, paste the macro code into a module in the VBE (ask if you need help).

    -Lenny

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •