PDA

View Full Version : selcting then hiding



fadib
09-02-2011, 11:44 AM
Guys,
I am looking at the best way to write this code.
I have created a randomn excel files with some data in it.
In sheet 1, the selection takes place. In sheet 2, lines gets hidden according to the selection in sheet 1.
The code can be written in two ways.
If the data are setup like in sheet 3, it is easy to right the code, but it takes time to put those "X".
On the other hand, if the data are setup like in Sheet 2, it is easy to see the data, but the code can be a bit tricky, and that is where I need some help.
Have you encountered similar subject?

fadib
09-02-2011, 12:55 PM
I modified the data.
maybe this make more sense.
Per the selection in Sheet1, the following lines in sheet 2 should remain unhidden: line 6, 8, 9.

Aussiebear
09-02-2011, 04:09 PM
Using your second workbook, I hid lines 3,4,5 &7 to follow your example, but nowhere can I see how you are able to determine why lines 6,8 & 9 had any relationship to "Noon". Can you recheck the workbook to ensure it is accurate and that it reflects what you are asking?

fadib
09-02-2011, 11:17 PM
Aussie sorry for the confusion.
Okay, I modified the data I think this will make more sense.
The code will take the first selection, which is "Monday" ---> all lines will remain.
Then It will check to see what is in column A.
If Time, then check what is the Time (Column). In here "Noon" ---> hide line 3, and 5.
Now it goes down to and check for the Type. Type is "Couple" it hides line 7.
Then If Column A contains "Time, Type" ---> Two Filtrations one per time and one per Type. that leads to hide line 8, 10.
If Column A contain Common, Keep line regardless.
So I should be left with line 4, 6, 9, and 11.

Aussie, I hope this help.

fadib
09-06-2011, 10:47 PM
I came up with this. It works.
I just want your input if the code can be written in a better way.
I don't have much experience in advance module or class module.
For instance, can I write a module, where I can feed the variables into it (For example: number of filtration 2, Filter by Date, Time, ....) and come up with an output (Hidding the row). This way I can make my code shorter and faster. And maybe get ride of the code using cell(,).select if possible.
Hope you guys, can help me, to achieve this milestone in my vba knowledge.
Thanks,

Private Sub CommandButton1_Click()
'//Declare all VBA variables
Dim LoopRange As Range
Dim RowTotalIndex As Integer
Dim Rowindex As Integer
Dim RowindexFilter As Integer
Dim avarSplit As Variant
Dim FilterCnt As Integer
Dim intIndex As Integer
Dim SelRow As Integer
Dim SelCol As Integer
Dim a As Integer
Dim i As Integer
Dim Txt As Variant
Dim l As Long
'End of VBA Variables\\
'//Declare all User Selection Variables
Dim SelDate As Variant
Dim SelTime As Variant
Dim SelType As Variant
'End of Selection Variables\\
RowindexFilter = 3
'The selection Performed
Sheet1.Activate
Sheet1.Cells(10, 1).Select
SelDate = Selection.Value
'MsgBox (SelDate)
Sheet1.Cells(10, 2).Select
SelTime = Selection.Value
'MsgBox (SelTime)
Sheet1.Cells(10, 3).Select
SelType = Selection.Value
'MsgBox (SelType)
'Comment: Find the number of rows which we will be filtering through
Sheet2.Activate
Cells(3, 1).Select
Set LoopRange = Range("A3", ActiveCell.End(xlDown))
RowTotalIndex = LoopRange.Count + 2
'Comment: Begining of loop
For Rowindex = 1 To RowTotalIndex
avarSplit = Split(Range("A" & Rowindex).Value, ",")
FilterCnt = UBound(avarSplit) + 1
'MsgBox "Row" & Rowindex & " has " & FilterCnt & " Filtration to perform"
For intIndex = LBound(avarSplit) To UBound(avarSplit)
'MsgBox "Filtration " & intIndex + 1 & " is, " & avarSplit(intIndex)
l = Application.WorksheetFunction.Match(avarSplit(intIndex), Range("A1:AE1"), 1)
'MsgBox "Found" & avarSplit(intIndex) & " at column : " & l
Cells(1, l).Select

'Comment: Find all the sub sections that corresponds to a specific cell
SelRow = ActiveCell.Row
SelCol = ActiveCell.Column
a = Selection.Columns.Count
'MsgBox "selected cell row is " & SelRow & " , and the Column is " & SelCol & _
" and the number of Subsection is " & a
For i = 1 To a
Cells(SelRow + 1, SelCol + i - 1).Select
Txt = Selection.Value

Select Case avarSplit(intIndex)
'Case Date
Case "Date"
If Txt = SelDate Then
'Comment: Hide every row that doesn't contain an X in the specific Column obtained from the selection
With Intersect(Columns(SelCol + i - 1), ActiveSheet.UsedRange)
'For RowindexFilter = 3 To RowTotalIndex
Rows(Rowindex).Select
If .Rows(Rowindex).Value = "" Then
Selection.EntireRow.Hidden = True
End If
'Next
End With
'MsgBox (Txt)
End If
'Case Time
Case "Time"
If Txt = SelTime Then
'Comment: Hide every row that doesn't contain an X in the specific Column obtained from the selection
With Intersect(Columns(SelCol + i - 1), ActiveSheet.UsedRange)
'For RowindexFilter = 3 To RowTotalIndex
Rows(Rowindex).Select
If .Rows(Rowindex).Value = "" Then
Selection.EntireRow.Hidden = True
End If
'Next
End With
'MsgBox (Txt)
End If
'Case Type
Case "Type"
If Txt = SelType Then
'Comment: Hide every row that doesn't contain an X in the specific Column obtained from the selection
With Intersect(Columns(SelCol + i - 1), ActiveSheet.UsedRange)
'For RowindexFilter = 3 To RowTotalIndex
Rows(Rowindex).Select
If .Rows(Rowindex).Value = "" Then
Selection.EntireRow.Hidden = True
End If
'Next
End With
'MsgBox (Txt)
End If

End Select
Next
Next
Next
End Sub