VBA obfuscate data & find cell

wigdawei

New Member
Joined
Jul 22, 2007
Messages
5
I am using Excel to tabulate scores for my employees. We work in a very busy and open office, so there is a need to be able to obfuscate the scores, but also help keep from losing my place while punching the scores.

I would like it to be able to return to the last cell that I was punching a score in... I believe I have become confused in using "ActiveCell.SpecialCells(xlLastCell).Select" as I think it wants to reselect the last used range as the last cell... How do I find the last used cell before the range of cells was selected? Any suggestions of a better approach to "hiding" the scores is also appreciated.

Thanks,
David

Code:
Sub Hide_Scores()
  Range("B15:EU35").Select
    ActiveSheet.Unprotect
       With Selection
          .HorizontalAlignment = xlCenter
          .VerticalAlignment = xlCenter
          .WrapText = False
          .Orientation = 0
          .AddIndent = False
          .IndentLevel = 0
          .ShrinkToFit = False
          .ReadingOrder = xlContext
          .MergeCells = False
        End With
        With Selection.Font
          .Name = "Arial"
          .FontStyle = "Bold"
          .Size = 10
          .Strikethrough = False
          .Superscript = False
          .Subscript = False
          .OutlineFont = False
          .Shadow = False
          .Underline = xlUnderlineStyleNone
          .ColorIndex = 2
        End With
      ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
  x = ActiveSheet.UsedRange.Rows.Count
  ActiveCell.SpecialCells(xlLastCell).Select
End Sub

EDIT: Added code tags - Moderator
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Hi
To find the last row use
x= cells(Rows.count,2).end(xlUP).row
you may need to unprotect the sheet.
Ravi
 
Upvote 0
Thank you for the response.

I have unprotected the sheet. However, the entire range B15:EU35 remains selected. When I begin typing the new data enters into cell B15 and not the last cell I used before the range was selected.

Other ideas?

- David
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,752
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top