VBA Button multiple click to unhide row

[Nechtan45]

Hi,

I'm having problems with a script in visual basics.
I have a button when clicked unhides a row and this is fine but I want to unhide more rows with this same button, ie unhide row 1 with one click then row 2 with second clikc etc.

Thanks in advance

Nechtan45

 

[kpark91]

post the code.

 

[baitmaster]

It's simple enough to unhide a row using a button, you just use a piece of code like

Rows(1).EntireRow.Hidden = False

Your problem here is that you want to change the line number. To do this you need to hold the number as a variable, either in a cell, or a name, or as a VBA variable

You then need to change this variable each time you run the macro

e.g.

public rowNum as integer
 
sub unhideRow
    Rows(rowNum).EntireRow.Hidden = False
    rownum = rownum + 1
end sub

You would need to add in code that resets rownum when desired

 

[Nechtan45]

public rowNum as integer

sub unhideRow
Rows(rowNum).EntireRow.Hidden = False
rownum = rownum + 1
end sub

so say its row 10 onwards I want to unhide per click

Public row(10) As Integer

Sub unhideRow()
rows(10).EntireRow.Hidden = False
rowNum = 10 + 1
End Sub

 

[baitmaster]

no not quite

You need to declare the variable rowNum as a "box" in which to store whatever number you want. Its declared publicly, outside the main macro, so it is always visible to VBA. This means the variable remains active after the code has stopped. You don't assign a value to it at this time, only create the box. The box is filled when you run your code

It would need to be set to your first value when you run it the first time. It then increments however you want each time you hit the button

At some point I guess you want to reset rownum, presumeably when you hide the rows again

public rowNum as integer
 
sub unhideRow
    if rownum = 0 then rownum = 10 ' this sets your defeult row number to row 10, only if rownum = 0, i.e. it hasn't yet been set
    Rows(rowNum).EntireRow.Hidden = False
    rownum = rownum + 1 ' this updates rownum, so next time it unhides row 11 instead. You could add 10 to it here instead
end sub
 
sub hideRows
rows("10:20").EntireRow.Hidden = true ' hides rows again
rownum = 10 ' resets rownum
end sub