| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
hiding rows
I've got a table that goes form rows 24 to 158 and from colums D to DC and I
want the macro to search colums S, AB, AK, AT, BC, BL, BU, CD, CM, CV, and if any cell in one of these columns has a number equal to or lower that -0.012 then that row I want shown, the rest hidden. How would I go about constructing a macro to do this? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
hiding rows
Here is some code. Blank columns will be made invisible.
Sub test() Dim SearchCol As Variant Dim difference As Double SearchCol = Array("S", "AB", "AK", "AT", "BC", "BL", "BU", "CD", "CM", "CV") For Each col In SearchCol found = False For RowCount = 24 To 158 If Cells(RowCount, col).Value <= -0.012 Then found = True Exit For End If Next RowCount If found = False Then Columns(col).Hidden = True End If Next col End Sub "Bumblebee" wrote: I've got a table that goes form rows 24 to 158 and from colums D to DC and I want the macro to search colums S, AB, AK, AT, BC, BL, BU, CD, CM, CV, and if any cell in one of these columns has a number equal to or lower that -0.012 then that row I want shown, the rest hidden. How would I go about constructing a macro to do this? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
hiding rows
I was interested in hiding the rows, those rows that contain a number 0<-0.012
"Joel" wrote: Here is some code. Blank columns will be made invisible. Sub test() Dim SearchCol As Variant Dim difference As Double SearchCol = Array("S", "AB", "AK", "AT", "BC", "BL", "BU", "CD", "CM", "CV") For Each col In SearchCol found = False For RowCount = 24 To 158 If Cells(RowCount, col).Value <= -0.012 Then found = True Exit For End If Next RowCount If found = False Then Columns(col).Hidden = True End If Next col End Sub "Bumblebee" wrote: I've got a table that goes form rows 24 to 158 and from colums D to DC and I want the macro to search colums S, AB, AK, AT, BC, BL, BU, CD, CM, CV, and if any cell in one of these columns has a number equal to or lower that -0.012 then that row I want shown, the rest hidden. How would I go about constructing a macro to do this? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
hiding rows
Easily fixed. Less than in math means from negative infinity to -0.012. You
want -0.012 < x <= 0. Sub test() Dim SearchCol As Variant Dim difference As Double SearchCol = Array("S", "AB", "AK", "AT", "BC", "BL", "BU", "CD", "CM", "CV") For Each col In SearchCol found = False For RowCount = 24 To 158 If (Cells(RowCount, col).Value -0.012) and _ (Cells(RowCount, col).Value <= 0) Then found = True Exit For End If Next RowCount If found = False Then Columns(col).Hidden = True End If Next col End Sub "Bumblebee" wrote: I was interested in hiding the rows, those rows that contain a number 0<-0.012 "Joel" wrote: Here is some code. Blank columns will be made invisible. Sub test() Dim SearchCol As Variant Dim difference As Double SearchCol = Array("S", "AB", "AK", "AT", "BC", "BL", "BU", "CD", "CM", "CV") For Each col In SearchCol found = False For RowCount = 24 To 158 If Cells(RowCount, col).Value <= -0.012 Then found = True Exit For End If Next RowCount If found = False Then Columns(col).Hidden = True End If Next col End Sub "Bumblebee" wrote: I've got a table that goes form rows 24 to 158 and from colums D to DC and I want the macro to search colums S, AB, AK, AT, BC, BL, BU, CD, CM, CV, and if any cell in one of these columns has a number equal to or lower that -0.012 then that row I want shown, the rest hidden. How would I go about constructing a macro to do this? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
hiding rows
Sorry, ignore my last question, when I read your answer I realized I didn't
really want to ask that. What I meant was that the rows in question are not hidden with the procedure you gave me, the firt one. "Joel" wrote: Easily fixed. Less than in math means from negative infinity to -0.012. You want -0.012 < x <= 0. Sub test() Dim SearchCol As Variant Dim difference As Double SearchCol = Array("S", "AB", "AK", "AT", "BC", "BL", "BU", "CD", "CM", "CV") For Each col In SearchCol found = False For RowCount = 24 To 158 If (Cells(RowCount, col).Value -0.012) and _ (Cells(RowCount, col).Value <= 0) Then found = True Exit For End If Next RowCount If found = False Then Columns(col).Hidden = True End If Next col End Sub "Bumblebee" wrote: I was interested in hiding the rows, those rows that contain a number 0<-0.012 "Joel" wrote: Here is some code. Blank columns will be made invisible. Sub test() Dim SearchCol As Variant Dim difference As Double SearchCol = Array("S", "AB", "AK", "AT", "BC", "BL", "BU", "CD", "CM", "CV") For Each col In SearchCol found = False For RowCount = 24 To 158 If Cells(RowCount, col).Value <= -0.012 Then found = True Exit For End If Next RowCount If found = False Then Columns(col).Hidden = True End If Next col End Sub "Bumblebee" wrote: I've got a table that goes form rows 24 to 158 and from colums D to DC and I want the macro to search colums S, AB, AK, AT, BC, BL, BU, CD, CM, CV, and if any cell in one of these columns has a number equal to or lower that -0.012 then that row I want shown, the rest hidden. How would I go about constructing a macro to do this? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
hiding rows
I'm confused by the wording of your question below. I think you want to
reverse which columns are hidden and which are not hidden. You have to just change True to False This code hides values that were not FOUND If found = False Then Columns(col).Hidden = True End If This code hides values that were FOUND If found = True Then Columns(col).Hidden = True End If "Bumblebee" wrote: Sorry, ignore my last question, when I read your answer I realized I didn't really want to ask that. What I meant was that the rows in question are not hidden with the procedure you gave me, the firt one. "Joel" wrote: Easily fixed. Less than in math means from negative infinity to -0.012. You want -0.012 < x <= 0. Sub test() Dim SearchCol As Variant Dim difference As Double SearchCol = Array("S", "AB", "AK", "AT", "BC", "BL", "BU", "CD", "CM", "CV") For Each col In SearchCol found = False For RowCount = 24 To 158 If (Cells(RowCount, col).Value -0.012) and _ (Cells(RowCount, col).Value <= 0) Then found = True Exit For End If Next RowCount If found = False Then Columns(col).Hidden = True End If Next col End Sub "Bumblebee" wrote: I was interested in hiding the rows, those rows that contain a number 0<-0.012 "Joel" wrote: Here is some code. Blank columns will be made invisible. Sub test() Dim SearchCol As Variant Dim difference As Double SearchCol = Array("S", "AB", "AK", "AT", "BC", "BL", "BU", "CD", "CM", "CV") For Each col In SearchCol found = False For RowCount = 24 To 158 If Cells(RowCount, col).Value <= -0.012 Then found = True Exit For End If Next RowCount If found = False Then Columns(col).Hidden = True End If Next col End Sub "Bumblebee" wrote: I've got a table that goes form rows 24 to 158 and from colums D to DC and I want the macro to search colums S, AB, AK, AT, BC, BL, BU, CD, CM, CV, and if any cell in one of these columns has a number equal to or lower that -0.012 then that row I want shown, the rest hidden. How would I go about constructing a macro to do this? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
hiding rows
Dim Cell As Range
Dim TestRow As Range Dim CellsInRow As String Rows.Hidden = False For Each TestRow In Range("A24:A158") CellsInRow = Replace("S#,AB#,AK#,AT#,BC#,BL#,BU#,CD#,CM#,CV#", "#", TestRow.Row) For Each Cell In Range(CellsInRow) If Cell.Value <= -0.012 Then Rows(Cell.Row).Hidden = True Exit For End If Next Cell Next TestRow "Bumblebee" wrote: I've got a table that goes form rows 24 to 158 and from colums D to DC and I want the macro to search colums S, AB, AK, AT, BC, BL, BU, CD, CM, CV, and if any cell in one of these columns has a number equal to or lower that -0.012 then that row I want shown, the rest hidden. How would I go about constructing a macro to do this? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
hiding rows
Thank you but the macro doesn't work. It doesn't seem to like the part:
CellsInRow = Replace("S#,AB#,AK#,AT#,BC#,BL#,BU#,CD#,CM#,CV#", "#", TestRow.Row) "Charlie" wrote: Dim Cell As Range Dim TestRow As Range Dim CellsInRow As String Rows.Hidden = False For Each TestRow In Range("A24:A158") CellsInRow = Replace("S#,AB#,AK#,AT#,BC#,BL#,BU#,CD#,CM#,CV#", "#", TestRow.Row) For Each Cell In Range(CellsInRow) If Cell.Value <= -0.012 Then Rows(Cell.Row).Hidden = True Exit For End If Next Cell Next TestRow "Bumblebee" wrote: I've got a table that goes form rows 24 to 158 and from colums D to DC and I want the macro to search colums S, AB, AK, AT, BC, BL, BU, CD, CM, CV, and if any cell in one of these columns has a number equal to or lower that -0.012 then that row I want shown, the rest hidden. How would I go about constructing a macro to do this? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Color alternate rows when after hiding selected rows | Excel Worksheet Functions | |||
| Hiding Specific Rows Based on Values in Other Rows | Excel Worksheet Functions | |||
| Hiding a button when hiding rows | Excel Discussion (Misc queries) | |||
| hiding Rows and buttons/comboxes, over the rows | Excel Programming | |||
| Hiding Rows if the linked rows are blank | Excel Discussion (Misc queries) | |||
Hybrid Mode
