Hide rows based on 2 conditions using excel macro
Solved
PLAYA2
Posted messages
15
Registration date
Status
Membre
Last intervention
-
PLAYA2 Posted messages 15 Registration date Status Membre Last intervention -
PLAYA2 Posted messages 15 Registration date Status Membre Last intervention -
Hello,
I would like to create a macro in Excel that will allow me to hide rows in an Excel file based on these 2 conditions:
If and only if the cells in columns AJ = X and AK = 0 (in number)
Could someone help me? I'm a beginner with macros and I've been pulling my hair out for 2 days...
Thanks in advance.
Configuration: Windows XP / Internet Explorer 8.0
I would like to create a macro in Excel that will allow me to hide rows in an Excel file based on these 2 conditions:
If and only if the cells in columns AJ = X and AK = 0 (in number)
Could someone help me? I'm a beginner with macros and I've been pulling my hair out for 2 days...
Thanks in advance.
Configuration: Windows XP / Internet Explorer 8.0
18 réponses
Hi,
The boss is right, why keep it simple when you can make it complicated
Sub Masque_lignes_condition_cellule()
Dim lg As Long, r As Long
Application.ScreenUpdating = False
With ActiveSheet.UsedRange
lg = .Row + .Rows.Count - 1
End With
For r = lg To 1 Step -1
If Cells(r, "AJ") Like "X" And Cells(r, "AK") Like "0" Then
Rows(r).EntireRow.Hidden = True
End If
Next r
Application.ScreenUpdating = True
End Sub
And tomorrow you’ll ask us how to display the hidden rows
--
Catch you later
Mike-31
A period of failure is a perfect time to sow the seeds of knowledge.
The boss is right, why keep it simple when you can make it complicated
Sub Masque_lignes_condition_cellule()
Dim lg As Long, r As Long
Application.ScreenUpdating = False
With ActiveSheet.UsedRange
lg = .Row + .Rows.Count - 1
End With
For r = lg To 1 Step -1
If Cells(r, "AJ") Like "X" And Cells(r, "AK") Like "0" Then
Rows(r).EntireRow.Hidden = True
End If
Next r
Application.ScreenUpdating = True
End Sub
And tomorrow you’ll ask us how to display the hidden rows
--
Catch you later
Mike-31
A period of failure is a perfect time to sow the seeds of knowledge.
This is what I proposed to my department head, but he wants a macro so that he only has to click a button to hide the unnecessary rows for reading...
This file is fed by a database (links) that updates daily. Moreover, he is not proficient in Excel and does not want to learn it.
Finally, I really want to meet his expectations but above all to get started with macros...
Can you please help me???
This file is fed by a database (links) that updates daily. Moreover, he is not proficient in Excel and does not want to learn it.
Finally, I really want to meet his expectations but above all to get started with macros...
Can you please help me???
Thank you very much MIKE 31. You can't imagine how happy you make me; I'm going to test that first thing tomorrow...
Hello,
Since you are new and starting with VBA, here is another documented method that sets up an automatic filter when you right-click on the sheet and shows everything again on the next right-click.
This might prevent (let's be cautious) tomorrow's question (hello Éric).
Copy this code into the sheet module to filter:
Copy, right-click on the tab, view code, paste
--
Best regards
Patrice
Since you are new and starting with VBA, here is another documented method that sets up an automatic filter when you right-click on the sheet and shows everything again on the next right-click.
This might prevent (let's be cautious) tomorrow's question (hello Éric).
' Module: Sheet1 ' Object: Automatic filtering of columns AJ and AK ' 01/08/13 ' ' ' Declaration of options for the entire sheet module ' A very good habit to adopt, always start a module with: Option Explicit ' ' Declaration of constants usable throughout the module ' (known to all procedures of the module) Private Const refCellule1 As String = "A1" 'Reference of the 1st cell of the table Private Const refColonne1 As String = "AJ" 'Reference of the 1st column to filter Private Const strCritère1 As String = "X" 'Filtering criterion for the 1st column Private Const refColonne2 As String = "AK" 'Reference of the 2nd column to filter Private Const strCritère2 As String = "0" 'Filtering criterion for the 2nd column ' ' Event procedures of the sheet module ' (executed automatically when one of the sheet's events occurs) ' Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) ' Update the filter at each right-click on the sheet (on the BeforeRightClick event) ' 01/08/13 ' 'Cancel the normal effect of the right-click (no display of the context menu) Cancel = True 'Call the filtering procedure indicating the sheet on which to apply the filter Call Filtrer_Colonnes(ActiveSheet) End Sub ' Other procedures of the module ' (executed only when called) ' Private Sub Filtrer_Colonnes(ByRef wsh As Worksheet) ' Procedure for filtering columns: Sets up / Removes the filter. ' Arguments: wsh [in] Sheet on which the filter will be applied ' 01/08/13 ' ' Declaration of variables usable in this procedure Dim rngTableau As Range 'Table containing the data Dim intChamp1 As Integer 'Number of the 1st field (1st column to filter) Dim intChamp2 As Integer 'Number of the 2nd field (2nd column to filter) 'If a filter is already in place... If wsh.FilterMode = True Then '... display all data and... wsh.ShowAllData '... exit the procedure. Exit Sub End If 'otherwise continue 'Define the range of cells containing the table Set rngTableau = wsh.Range(refCellule1).CurrentRegion 'If the automatic filter is not set, activate it. If Not wsh.AutoFilterMode Then rngTableau.AutoFilter 'Define the field numbers to filter = column number of the sheet intChamp1 = wsh.Columns(refColonne1).Column intChamp2 = wsh.Columns(refColonne2).Column 'Set up filtering for the two fields rngTableau.AutoFilter Field:=intChamp1, Criteria1:=strCritère1 rngTableau.AutoFilter Field:=intChamp2, Criteria1:=strCritère2 End Sub
Copy this code into the sheet module to filter:
Copy, right-click on the tab, view code, paste
--
Best regards
Patrice
Thank you, Patrice, for these elements.
In fact, I managed to get by without entering in VBA => I cheated by applying filters that I saved in my macro, so my boss is happy.
Of course, he asked me for another macro to hide entire pages (with column headers) => 1:133) if a specific row (129) is empty.
There I can't use filters? So I tried this:
Rows("1:133").Select
If Cells("AK129") = 0 Then
End If
Selection.EntireRow.Hidden = True
End sub
But it doesn't work, could someone enlighten me please? For your information, the file contains several pages in a row for which I need to repeat the same operation, but if I have the right syntax for one row, I should manage the rest.
Thanks in advance.
PS: Thanks again, MIKE 31, but it didn't work; indeed, on the line
If Cells(r, "AJ") Like "X" And Cells(r, "AK") Like "0" Then
I got the message: "syntax error", but it still helped me to see the language to use...
In fact, I managed to get by without entering in VBA => I cheated by applying filters that I saved in my macro, so my boss is happy.
Of course, he asked me for another macro to hide entire pages (with column headers) => 1:133) if a specific row (129) is empty.
There I can't use filters? So I tried this:
Rows("1:133").Select
If Cells("AK129") = 0 Then
End If
Selection.EntireRow.Hidden = True
End sub
But it doesn't work, could someone enlighten me please? For your information, the file contains several pages in a row for which I need to repeat the same operation, but if I have the right syntax for one row, I should manage the rest.
Thanks in advance.
PS: Thanks again, MIKE 31, but it didn't work; indeed, on the line
If Cells(r, "AJ") Like "X" And Cells(r, "AK") Like "0" Then
I got the message: "syntax error", but it still helped me to see the language to use...
Hello,
To detect a 0 in a cell, two conditions are required:
- cell not empty
and
- value = 0
because Excel considers that an empty cell contains a value equal to 0
Your code to hide rows 1:133 becomes:
--
Best regards
Patrice
To detect a 0 in a cell, two conditions are required:
- cell not empty
and
- value = 0
because Excel considers that an empty cell contains a value equal to 0
Your code to hide rows 1:133 becomes:
Sub xxx() If Cells(129, "AK").Value = 0 And Not IsEmpty(Cells(129, "AK").Value) Then Rows("1:133").Hidden = True Else Rows("1:133").Hidden = False End If End Sub --
Best regards
Patrice
Ok, thank you very much.
We can say that you are particularly patient...
I'll see about that tomorrow at work!!!
Have a good evening.
We can say that you are particularly patient...
I'll see about that tomorrow at work!!!
Have a good evening.
Hi Patrice 33740,
As agreed, I tested the macro:
If Cells(129, "AK").Value = 0 And Not IsEmpty(Cells(129, "AK").Value) Then
Rows("1:133").Hidden = True
Else
Rows("1:133").Hidden = False
End If
End Sub
But I get the message => compilation error: Else without IF
The Excel version is indeed 2002, maybe that’s the reason???
What do you think???
Thanks in advance.
As agreed, I tested the macro:
If Cells(129, "AK").Value = 0 And Not IsEmpty(Cells(129, "AK").Value) Then
Rows("1:133").Hidden = True
Else
Rows("1:133").Hidden = False
End If
End Sub
But I get the message => compilation error: Else without IF
The Excel version is indeed 2002, maybe that’s the reason???
What do you think???
Thanks in advance.
Hello, it's me again. This time I have an issue with redisplaying the rows:
I have 2 macros in the same file => the 1st one starting from a filter action:
Sub Macrolignes()
'
' Columns("AJ:AJ").Select
selection.AutoFilter
selection.AutoFilter Field:=1, Criteria1:="=A", Operator:=xlOr, _
Criteria2:="<>0"
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
End Sub
And the second macro:
Sub Macropage()
'
'
If Cells(129, "AK").Value = 0 And Not IsEmpty(Cells(129, "AK").Value) Then
Rows("8:133").Hidden = True
Else
Rows("8:133").Hidden = False
End If
End Sub
And to unhide, I do it manually => Unhide rows
I'm encountering the following problem: I can run both macros in sequence without issue, but I can no longer unhide; this is only possible by running one of the 2 macros.
Is it possible with Excel 2002, and if so, can we compile these 2 macros into one???
Thank you in advance.
I have 2 macros in the same file => the 1st one starting from a filter action:
Sub Macrolignes()
'
' Columns("AJ:AJ").Select
selection.AutoFilter
selection.AutoFilter Field:=1, Criteria1:="=A", Operator:=xlOr, _
Criteria2:="<>0"
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
End Sub
And the second macro:
Sub Macropage()
'
'
If Cells(129, "AK").Value = 0 And Not IsEmpty(Cells(129, "AK").Value) Then
Rows("8:133").Hidden = True
Else
Rows("8:133").Hidden = False
End If
End Sub
And to unhide, I do it manually => Unhide rows
I'm encountering the following problem: I can run both macros in sequence without issue, but I can no longer unhide; this is only possible by running one of the 2 macros.
Is it possible with Excel 2002, and if so, can we compile these 2 macros into one???
Thank you in advance.
Re,
Your macros do not match the problem statement!
It is certainly possible, but we need to know exactly what you want?
Clearly explain the entirety of the need:
- Which lines need to be hidden and under what conditions
- What are the conditions for "unhiding"
- How is the macro executed? by a button, several buttons or by one or more events?
Patrice
Your macros do not match the problem statement!
It is certainly possible, but we need to know exactly what you want?
Clearly explain the entirety of the need:
- Which lines need to be hidden and under what conditions
- What are the conditions for "unhiding"
- How is the macro executed? by a button, several buttons or by one or more events?
Patrice
Actually, the initial request was to hide certain rows at 0 (but not all of them, like the header rows...) => macro 1, executed by a button
Then, the second request was to hide all empty pages => macro 2, executed by a second button.
But we should be able to cancel either one or both macros according to the user's preference...
Ideally, macro 1 would run upon opening the file, and macro 2 could be launched whenever desired, with the ability to cancel each operation based on preference.
Of course, that would be ideal, but running both macros and being able to redisplay would be great.
I hope I have responded clearly enough.
P.S.: I noticed that you must run macro 1 before macro 2; otherwise, it doesn't work...
Then, the second request was to hide all empty pages => macro 2, executed by a second button.
But we should be able to cancel either one or both macros according to the user's preference...
Ideally, macro 1 would run upon opening the file, and macro 2 could be launched whenever desired, with the ability to cancel each operation based on preference.
Of course, that would be ideal, but running both macros and being able to redisplay would be great.
I hope I have responded clearly enough.
P.S.: I noticed that you must run macro 1 before macro 2; otherwise, it doesn't work...
Regarding Patrice,
In the end, don’t bother, I found the solution by removing all filters when I want to unmask and restart the desired macro based on the need.
The only downside is for the automatic launch of macro 1 upon opening the file.
I did find this discussion on the forum, but it doesn’t work; maybe it’s due to the version of Excel???
I’ll keep searching....
Thanks again.
In the end, don’t bother, I found the solution by removing all filters when I want to unmask and restart the desired macro based on the need.
The only downside is for the automatic launch of macro 1 upon opening the file.
I did find this discussion on the forum, but it doesn’t work; maybe it’s due to the version of Excel???
I’ll keep searching....
Thanks again.
Re,
I would like to return to the discussion; the issue is not the writing of code but clearly understanding the expectations from the start and being provided with an annotated example file, otherwise we will work and make proposals for nothing.
--
A+
Mike-31
A period of failure is a perfect time to sow the seeds of knowledge.
I would like to return to the discussion; the issue is not the writing of code but clearly understanding the expectations from the start and being provided with an annotated example file, otherwise we will work and make proposals for nothing.
--
A+
Mike-31
A period of failure is a perfect time to sow the seeds of knowledge.
Hi Mike-31,
Thank you very much for still being interested in my difficulties, but it's okay; I have managed to achieve all my goals.
At least for now, since as soon as I find a solution, my boss presents me with a new challenge!!!
I certainly wouldn't have made it on my own.
THANK YOU again to both of you.
See you later.
Thank you very much for still being interested in my difficulties, but it's okay; I have managed to achieve all my goals.
At least for now, since as soon as I find a solution, my boss presents me with a new challenge!!!
I certainly wouldn't have made it on my own.
THANK YOU again to both of you.
See you later.