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
You see, from what I understand from your code ( i know just the basics)
Columns(col).Hidden = True means we are talking about hiding columns, and I am interested in hiding rows "Joel" wrote: 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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
hiding rows
you see, I am looking for something like this but I want more than column 19
checked for the condition, I want a number of columns checked. Besides, this procedure is quite slow but it works, at least for one column BeginRow = 24 EndRow = 158 ChkCol = 19 For RowCnt = BeginRow To EndRow If Cells(RowCnt, ChkCol).Value <= -0.012 Then Cells(RowCnt, ChkCol).EntireRow.Hidden = False Else Cells(RowCnt, ChkCol).EntireRow.Hidden = True End If Next RowCnt "Bumblebee" wrote: You see, from what I understand from your code ( i know just the basics) Columns(col).Hidden = True means we are talking about hiding columns, and I am interested in hiding rows "Joel" wrote: 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? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
hiding rows
I just reverse the rows and columns. Looking at your code it is better to
reference columns using letters instead of this cells(5,19) use this cells(5,"S") Who remembers that the letter S is the 19th letter of the alphabet. Or like you did it in your code chkCol = "S" cells(5,chkCol) Sub test() Dim SearchCol As Variant Dim difference As Double SearchCol = Array("S", "AB", "AK", "AT", "BC", "BL", "BU", "CD", "CM", "CV") For RowCount = 24 To 158 found = False For Each col In SearchCol If Cells(RowCount, col).Value <= -0.012 Then found = True Exit For End If Next col If found = False Then Rows(RowCount).Hidden = True End If Next RowCount End Sub "Bumblebee" wrote: you see, I am looking for something like this but I want more than column 19 checked for the condition, I want a number of columns checked. Besides, this procedure is quite slow but it works, at least for one column BeginRow = 24 EndRow = 158 ChkCol = 19 For RowCnt = BeginRow To EndRow If Cells(RowCnt, ChkCol).Value <= -0.012 Then Cells(RowCnt, ChkCol).EntireRow.Hidden = False Else Cells(RowCnt, ChkCol).EntireRow.Hidden = True End If Next RowCnt "Bumblebee" wrote: You see, from what I understand from your code ( i know just the basics) Columns(col).Hidden = True means we are talking about hiding columns, and I am interested in hiding rows "Joel" wrote: 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? |
#10
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? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
hiding rows
Sorry for taking so long to get back to you. I have just tried the macro and
it works. Thank you. "Joel" wrote: I just reverse the rows and columns. Looking at your code it is better to reference columns using letters instead of this cells(5,19) use this cells(5,"S") Who remembers that the letter S is the 19th letter of the alphabet. Or like you did it in your code chkCol = "S" cells(5,chkCol) Sub test() Dim SearchCol As Variant Dim difference As Double SearchCol = Array("S", "AB", "AK", "AT", "BC", "BL", "BU", "CD", "CM", "CV") For RowCount = 24 To 158 found = False For Each col In SearchCol If Cells(RowCount, col).Value <= -0.012 Then found = True Exit For End If Next col If found = False Then Rows(RowCount).Hidden = True End If Next RowCount End Sub "Bumblebee" wrote: you see, I am looking for something like this but I want more than column 19 checked for the condition, I want a number of columns checked. Besides, this procedure is quite slow but it works, at least for one column BeginRow = 24 EndRow = 158 ChkCol = 19 For RowCnt = BeginRow To EndRow If Cells(RowCnt, ChkCol).Value <= -0.012 Then Cells(RowCnt, ChkCol).EntireRow.Hidden = False Else Cells(RowCnt, ChkCol).EntireRow.Hidden = True End If Next RowCnt "Bumblebee" wrote: You see, from what I understand from your code ( i know just the basics) Columns(col).Hidden = True means we are talking about hiding columns, and I am interested in hiding rows "Joel" wrote: 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? |
#12
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) |