question about hiding rows/columns and clearing worksheet

[shorty123]

I have some code which is trying to hide columns/rows based on some logic. After the rows/columns are hidden, the visible rows/columns are copied to Sheet2. Then I am trying to unhide the rows and columns in Sheet1.

1) In the beginning, I am trying to see if cell C3 in Sheet1 has changed. How do I modify that logic to specify that cell C3 needs to change in Sheet 1? What does this logic need to look like if I want to use r1c1 notation? I know that it is better to keep the entire script in one notation (A1 or R1C1).

2) How do I unhide the rows/columns in Sheet1?

3) how do I hide a row (what code needs to replace "MsgBox("hide the row"))?

4) how do I hide a column? (what code needs to replace "MsgBox("hide the column"))?

5) How should the intersect logic be rewritten to be in r1c1 notation? (If Not Intersect(Target, Range("C3:C3")) Is Nothing Then)

6) How do I copy Sheet 1 to Sheet 2 using r1c1 notation? My code is using A1 notation.

Here is my code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i, rowNum, colNum As Integer
Dim name As String
Dim found As Boolean
 ' when the value of cell C3 changes, some processing
 ' needs to be done
 
 ' how do you check to see if the cell which changed
 ' was in Sheet1???
 If Not Intersect(Target, Range("C3:C3")) Is Nothing Then
    colNum = 11
    ' from column 11 to the end of the table, check if
    ' the cell associated with row 5 has a name
    name = Cells(5, colNum).Value
    Do While (name <> "")
       If (name <> Target.Value) Then
          ' how do you hide the entire column in r1c1 notation?
          'Cell.EntireColumn("???").Hidden = True
           MsgBox ("hide the column")
       Else
          ' don't hide the column
          ' Cell.EntireColumn("???").Hidden = False
           MsgBox ("don't hide the column")
        End If
        colNum = colNum + 1
        name = Cells(5, colNum)
    Loop
    
    ' loop through the table starting from row 7
    rowNum = 7
    Do While (Cells(rowNum, 1) <> "")
        found = False
  
        ' loop through the table starting from column 11
        colNum = 11
        name = Cells(5, colNum)
        Do While ((name <> "") And (found <> True))
            ' in columns 11 until the end of the table
            ' check if any cells in the row contain a value
            If (Cells(rowNum, colNum).Value <> "") Then
                found = True
            End If
            name = Cells(5, colNum).Value
            colNum = colNum + 1
        Loop
        If found <> True Then
           ' hide the row for rowNum
           MsgBox ("hide the row")
        Else
           MsgBox ("don't hide the row")
        End If
        rowNum = rowNum + 1
    Loop
 End If
 
 ' copy the Sheet1 to the worksheet called "Sheet2"
 ' the hidden rows and hidden columns should not show up
 Sheets("Sheet1").Select
 Range("A1").Select
 Selection.CurrentRegion.Select
 Application.CutCopyMode = False
 Selection.Copy
 Sheets("output").Select
 
 ' unhide all rows and columns in Sheet1
 
End Sub

 

[shorty123]

Thanks a lot AlphaFrog. I see now that the one cell in range case needs to be handled as a special case. You are really an Excel guru. I really appreciate your help.

 

[AlphaFrog]

You're welcome. If I were a true Excel jedi guru master, I suppose I should have gotten it correct the first time.