i'm looking for a way to create a button in excell that can be placed within
a spreadsheet to control the visiblity of a fixed number of rows. i
currently have the following code in place:

Private Sub CommandButton1_Click()
Dim myRng As Range

Set myRng = Me.Range("a2:a10")

myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)

End Sub

Private Sub CommandButton2_Click()
Dim myRng As Range

Set myRng = Me.Range("a12:a20")

myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)

End Sub

I am not VB savvy in the slightest sense and thankfully found this code
online, but I ran into a problem once a row is inserted between rows
containing the command button. Example:

Row 1 has Command Button 1 that controls the hiding/unhiding of rows 2-10
Row 11 has a Command Button 2 that controls the hiding/unhiding of rows 12-20

I insert a row above Row 11 (Command Button 2 shifts along with the row,
which is ideal), but by doing so the range in the VB code does not adjust to
the downward shift, causing Command Button 2's code to pertain to the wrong
range now that the button is in Row 12.

Is there a way to create a hide/unhide button with coding that adjusts it's
range after it's commanding button is shifted into a different row?

- cheetah

Try this:

'This subroutine will hide an entire row if the value in a certain column,
'in this case column "T", is blank.
Sub hide_rows()
Dim RowNdx As Long
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "T").Value = "" Then
Rows(RowNdx).RowHeight = 0
End If
Next RowNdx
End Sub

'This macro will hide all of the blank rows in the active
'worksheet or in the selection. If the current selection
'covers more than one row, only blank rows in those rows
'will be deleted. Otherwise, all blank rows in the entire
'worksheet will be deleted. The entire row must be blank
'for the row to be deleted.
Public Sub HideBlankRows()
Dim R As Long
Dim C As Range
Dim Rng As Range
On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
If Selection.Rows.Count 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(R).E ntireRow) = 0 Then
Rng.Rows(R).RowHeight = 0
End If
Next R
EndMacro:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"cheetah" wrote:

i'm looking for a way to create a button in excell that can be placed within
a spreadsheet to control the visiblity of a fixed number of rows. i
currently have the following code in place:

Private Sub CommandButton1_Click()
Dim myRng As Range

Set myRng = Me.Range("a2:a10")

myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)

End Sub

Private Sub CommandButton2_Click()
Dim myRng As Range

Set myRng = Me.Range("a12:a20")

myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)

End Sub

I am not VB savvy in the slightest sense and thankfully found this code
online, but I ran into a problem once a row is inserted between rows
containing the command button. Example:

Row 1 has Command Button 1 that controls the hiding/unhiding of rows 2-10
Row 11 has a Command Button 2 that controls the hiding/unhiding of rows 12-20

I insert a row above Row 11 (Command Button 2 shifts along with the row,
which is ideal), but by doing so the range in the VB code does not adjust to
the downward shift, causing Command Button 2's code to pertain to the wrong
range now that the button is in Row 12.

Is there a way to create a hide/unhide button with coding that adjusts it's
range after it's commanding button is shifted into a different row?

- cheetah