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 create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
To find the last row use
x= cells(Rows.count,2).end(xlUP).row
you may need to unprotect the sheet.
Ravi
 

wigdawei

New Member
Joined
Jul 22, 2007
Messages
5
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
 

wigdawei

New Member
Joined
Jul 22, 2007
Messages
5
The cursor de-selects the range (GREAT!) but then sits at cell B15....

Thanks again.

- David
 

Forum statistics

Threads
1,181,362
Messages
5,929,535
Members
436,676
Latest member
Mavri

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
Top