Unhiding a Table of numbers one row at a Time

[robreich]

I have a Table of numbers that is 10 columns wide x up to 50 rows long (Cells A1 thru J50 maximum). For presentation purposes, I need to hide the entire table and then unhide one row at a time, starting with row 1, then row 2, etc until all 50 rows are visible. I want to be able to control when each row is unhidden, as the time between each row will vary depending on other factors. I have been able to get a macro to unhide a hidden row, but after that, I am doing something wrong, because I can't get it to unhide multiple rows unless I do them all at once. I have not even tried to figure out how to do them one at a time with a pause in between, length of which I can control.

Any help that anyone can give me would be greatly appreciated.

Bob

 

[Rick Rothstein]

I have a Table of numbers that is 10 columns wide x up to 50 rows long (Cells A1 thru J50 maximum). For presentation purposes, I need to hide the entire table and then unhide one row at a time, starting with row 1, then row 2, etc until all 50 rows are visible. I want to be able to control when each row is unhidden, as the time between each row will vary depending on other factors. I have been able to get a macro to unhide a hidden row, but after that, I am doing something wrong, because I can't get it to unhide multiple rows unless I do them all at once. I have not even tried to figure out how to do them one at a time with a pause in between, length of which I can control.

Maybe you can make use of this concept... use cell formatting to hide and unhide the rows of data from your table. This macro can be used to hide the table's values..

Sub HideTableData()
  Range("A1:J50").NumberFormat = ";;;"
End Sub

And each time you execute this next macro, another row of values from the table will be revealed...

Sub ShowNextTableRow()
  Dim R As Long
  For R = 1 To 50
    If Cells(R, "A").Resize(, 10).NumberFormat = ";;;" Then
      Cells(R, "A").Resize(, 10).NumberFormat = "General"
      Exit Sub
    End If
  Next
End Sub

If you actually wanted to physically hide the entire rows (that way a user cannot click on a "hidden" cell and see what's in it by simply looking at the Formula Bar), then you can use this macro to reveal hidden rows one at a time...

Sub ShowNextTableRow()
  Dim R As Long, CurrentCell As Range
  For R = 1 To 50
    If Rows(R).Hidden Then
      Rows(R).Hidden = False
      If R = 1 Then
        Set CurrentCell = ActiveCell
        Range("A1").Activate
        CurrentCell.Activate
      End If
      Exit Sub
    End If
  Next
End Sub

 

[robreich]

Rick,

Thanks. Your last macro is the one I will use. I want to have the entire Table hidden. I copied your Code and it does what I want.

Bob

 

[robreich]

Rick,

By any chance do you know how to make a Button FLOAT so that as I scroll down, the Button stays on my screen? I want to tie your macro for unhiding rows to a button so that I can just click on it to execute the macro. I have found a couple of things on the internet, but neither of them has worked.

Bob

 

[robreich]

Rick,
Ignore my question about the floating button. I figured out how to put the macro up on my TOOL BAR and that works just as well.

Bob

 

[robreich]

Rick,
You helped me last October creating a Random Number generator table, assuming 500 numbers (1 - 500). As I explained back then, this is being used for a Reverse Raffle at a Charity Function in Ohio. A question has come up as we approach our first use of the macro this coming October. What if all of the 500 tickets are not sold, i.e. they are being sold by multiple people that are members of the organization that is sponsoring the Charity Function. Obviously, if the last ticket sold is less than 500, we simply enter that as the LAST NUMBER. However, is there a way to deal with the issue if Tickets Number 275 thru 280 do not sell, 340 thru 345 do not sell, etc.? Right now, we are assuming that their are no unsold tickets in the Table of Numbers. One of the reasons of trying to address this is that every 10th ticket drawn wins a $25.00 Gift Card. If we simply put the unsold tickets up on the board and remove them as they are "drawn", then the "unsold tickets" mess up the Gift Cards.

I was thinking along the lines of building a Table Range that contains all of the SOLD Tickets only. Then, having your macro pull the random numbers from this Table Range. Not sure if that makes sense or it is doable, but if you could help me with this again, it would be greatly appreciated.

Bob