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:
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:
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
