Macro to add to or subtract from identified row numbers in multiple macros

tectonicseer

New Member
Joined
Jul 22, 2014
Messages
1
I have a spreadsheet that, when prompted by a list box, will hide or unhide rows in specified groupings. I will need to add and delete a few more rows throughout this list and by doing so will make the VBA script (sample below) non-functional (by "pointing" at the wrong rows). This script is repeated about 24-30 times covering approximately 700 rows. These groupings are sequential and do not overlap one another.

Question: is there a macro I can use to automatically renumber a range of rows by a certain +/- value, and then another range with a different value?

Thanks for your insights! David
Code:
Sub ProcessSheet1ChangeOnCellJ7(ByVal Target As Range)


  Dim sAddress As String
  Dim sValue As String
  
  'Get the address of the cell that changed without '$' signs
  sAddress = Target.Address(False, False)
  
  'Get the value in the cell without leading and trailing spaces
  'Allow processing to continue if the cell does not contain text
  On Error Resume Next
  sValue = Trim(Target.Value)
  On Error GoTo 0


  'Perform 'J7' only processing
  If sAddress = "J7" Then
    ' HIDE ROWS IN TASKS WORKSHEET BASED ON USER SELECTION
    If Target.Value = "Not Pursuing" Then
        ActiveWorkbook.Sheets("Tasks").Rows("29:29").EntireRow.Hidden = False
        ActiveWorkbook.Sheets("Tasks").Rows("30:48").EntireRow.Hidden = True
    ElseIf Target.Value = "Pursuing - Show All Tasks" Then
        ActiveWorkbook.Sheets("Tasks").Rows("29:29").EntireRow.Hidden = True
        ActiveWorkbook.Sheets("Tasks").Rows("30:48").EntireRow.Hidden = False
    Else
        'This branch of code should NEVER be selected.
        MsgBox "SOFTWARE INTEGRITY ERROR.  Illegal selection on:" & vbCrLf & _
               "Sheet 'Tasks'" & vbCrLf & _
               "Cell: '" & sAddress & "'" & vbCrLf & _
               "Value: '" & sValue & "'"
        Stop
    End If
  End If
    
End Sub
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Watch MrExcel Video

Forum statistics

Threads
1,102,288
Messages
5,485,909
Members
407,523
Latest member
Talicius

This Week's Hot Topics

Top