Excel Macro/VBA that will hide rows based on multiple criteria

[riverkee]

I would like to hide rows for data that does not meet specific criteria. For example: If a user selects room number 101 from a drop down in B1, I want to filter data in range A3:F1000 to show me only room 101 rows (A column) where C column contains a value larger than zero OR D column contains a value larger than zero OR E column contains a value larger than zero. I do not want it to return rows where 101 may be in other columns beside A.

Sample:

A B C D E F
__________________________________
1 ROOM: 101
2
3
4 101 XX 1.2 0 0 P
16 101 YA 0 0 1.1 L
23 101 JJ 3.2 2.1 0 L
55 101 JJ 0 0 1 P


So, if a row contains 101 in column A and all three values in columns C, D, and E equal 0, then those rows will be hidden.

Any ideas how I can write that macro? Any help would be greatly appreciated.

 

[venkat1926]

your requirement is not completely clear. your sample data also is not complete. there is no 101 in column A from row 3 downward. no validation list in B1. I have added G column also for autofiler. filtered data is what,I presume, you want.

I have redesigned the ample sheet

see the ample sheet below

Excel Workbook
	A	B	C	D	E	F	G
1	1	101
2	2
3	3
4	4	101	XX	1.2	0	0	P
5	16	101	YA	0	0	1.1	L
6	23	101	JJ	3.2	2.1	0	L
7	55	101	JJ	0	0	1	P
8	101		1	2	3	4
9	101		1		3	4
10	101			5	5
Sheet1

now try this macro and check whether it gives what you want.
I am using autofilter for which column headings are necessary. your data range is from row 3. so I have taken data range from row 2(row 2 is apparent column headings)

Sub test()
Dim source As Range, r As Range, filt As Range
Dim x As Integer, j As Integer
ActiveSheet.AutoFilterMode = False
Set source = Range("B1")
Set r = Range("a2:G1000")
r.AutoFilter Field:=1, Criteria1:=101
j = WorksheetFunction.CountA(r.Columns(1).SpecialCells(12))

If j = 2 Then GoTo eexit
r.AutoFilter Field:=3, Criteria1:=">0"
r.AutoFilter Field:=4, Criteria1:=">0"
r.AutoFilter Field:=5, Criteria1:=">0"

If WorksheetFunction.CountA(r.Columns(1).SpecialCells(12)) = 1 Then
GoTo continue
End If

GoTo eexit

continue:
 MsgBox "no such rows"
eexit:


End Sub

 

[venkat1926]

your requirement is not completely clear. your sample data also is not complete. there is no 101 in column A from row 3 downward. no validation list in B1. I have added G column also for autofiler. filtered data is what,I presume, you want.

I have redesigned the ample sheet

see the ample sheet below

Excel Workbook
	A	B	C	D	E	F	G
1	1	101	*	*	*	*	*
2	2	*	*	*	*	*	*
3	3	*	*	*	*	*	*
4	4	101	XX	1.2	0	0	P
5	16	101	YA	0	0	1.1	L
6	23	101	JJ	3.2	2.1	0	L
7	55	101	JJ	0	0	1	P
8	101	*	1	2	3	4	*
9	101	*	1	*	3	4	*
10	101	*	*	5	5	*	*
Sheet1

now try this macro and check whether it gives what you want.
I am using autofilter for which column headings are necessary. your data range is from row 3. so I have taken data range from row 2(row 2 is apparent column headings)

Sub test()
Dim source As Range, r As Range, filt As Range
Dim x As Integer, j As Integer
ActiveSheet.AutoFilterMode = False
Set source = Range("B1")
Set r = Range("a2:G1000")
r.AutoFilter Field:=1, Criteria1:=101
j = WorksheetFunction.CountA(r.Columns(1).SpecialCells(12))

If j = 2 Then GoTo eexit
r.AutoFilter Field:=3, Criteria1:=">0"
r.AutoFilter Field:=4, Criteria1:=">0"
r.AutoFilter Field:=5, Criteria1:=">0"

If WorksheetFunction.CountA(r.Columns(1).SpecialCells(12)) = 1 Then
GoTo continue
End If

GoTo eexit

continue:
 MsgBox "no such rows"
eexit:


End Sub

 

[riverkee]

The current macro did not return the results I needed. I apologize for not being clearer. Maybe this help:

What it looks like before the macro is run:
<a href="http://www.flickr.com/photos/17041410@N03/7261542480/" title="Before by rvrk33, on Flickr"><img src="http://farm8.staticflickr.com/7244/7261542480_2b41c61115.jpg" width="476" height="440" alt="Before"></a>


What it should look like after the macro is run:
<a href="http://www.flickr.com/photos/17041410@N03/7261542546/" title="After by rvrk33, on Flickr"><img src="http://farm9.staticflickr.com/8005/7261542546_0d0222be9f.jpg" width="477" height="202" alt="After"></a>

B1 is the variable. So, if I put in 102 and run the macro, it should only display rows with 102 where Var 1, Var 2, or Var 3 have a value greater than zero. I should point out that I need rows 2 and 3 to blank.

I appreciate the time you're taking to help. Thank you very much.

 

[venkat1926]

sorry. You have given the table as an image. I do not know how to transfer the data in that image to an excel sheet.This is necessary to help me in modifying the macro and testing it. Perhaps you are not aware of the excel geanie.html facility whereby the data in a sheet(small volume of data) can be attached to the messages in this newsgsroup (last item in the first sticky thread in the newsgroup first page-

http://www.mrexcel.com/forum/showthread.php?t=89356

see whether you can send it using this addin. or sending row by row with "comma" delimiter

see if possible to send it as attachment(see below).the reply will be sent only in the newsgroup thread

 

[riverkee]

I apologize again. I am unable to install software/plugins. I did create a delimiter using semicolons. You should be able to paste this into a .txt file to import. It worked when I tested it. Thanks again. Data:


Room;101


Room;Type;Var A;Var B;Var C;Code
101;XX;1.2;0;0;P
100;DD;1.3;1.1;0;L
103;AA;0;0;0;L
105;GG;0;1;0;L
106;GG;1;1;1;P
121;KK;1.2;0;1.1;P
120;LL;1.1;1.1;0;L
101;YA;0;0;1.1;L
100;XX;0;1.2;0;P
104;DD;1.1;0;0;L
105;XX;1.2;0;0;L
101;JJ;0;0;1;P
101;XX;0;0;0;P
101;JJ;0;0;0;L
101;GG;0;0;0;P

 

[venkat1926]

that is one of the good ways of sending data as it is easy to copy into excel sheet for writing macro and testing.

see your data in sheet 1 here

Excel Workbook
	A	B	C	D	E	F
1	room	101
2
3
4
5
6	Room	Type	Var A	Var B	Var C	Code
7	101	XX	1.2	0	0	P
8	100	DD	1.3	1.1	0	L
9	103	AA	0	0	0	L
10	105	GG	0	1	0	L
11	106	GG	1	1	1	P
12	121	KK	1.2	0	1.1	P
13	120	LL	1.1	1.1	0	L
14	101	YA	0	0	1.1	L
15	100	XX	0	1.2	0	P
16	104	DD	1.1	0	0	L
17	105	XX	1.2	0	0	L
18	101	JJ	0	0	1	P
19	101	XX	0	0	0	P
20	101	JJ	0	0	0	L
21	101	GG	0	0	0	P
Sheet1

the actual data is from 6th row. dont worry as long as there is no data between
A1 and the beginning of data column A the macro will find out in which row the data begins.; study the macro carefully otherwise the configuration should remain same. for e.g. the room number to be filtered should be in B1 only

the macro automatically undo the result of the macro and runs the balance codes.

for retesting just run the macro test.

Sub test()
Dim source As Range, r As Range, filt As Range
Dim x As Integer, j As Integer
Dim k As Integer, m As Integer, rbeg As Range

Worksheets("sheet1").Activate
ActiveSheet.AutoFilterMode = False
x = Range("B1").Value
Set source = Range("B1")
Set rbeg = Range("a1").End(xlDown)
'MsgBox rbeg.Address

Set r = Range(rbeg, rbeg.End(xlDown))
'MsgBox r.Address
m = rbeg.End(xlDown).Row
r.AutoFilter Field:=1, Criteria1:=x
If WorksheetFunction.CountA(r.Columns(1).SpecialCells(12)) = 1 Then
GoTo continue
j = WorksheetFunction.CountA(r.Columns(1).SpecialCells(12))

If j = 2 Then GoTo eexit

Set filt = r.SpecialCells(xlCellTypeVisible)


End If

For k = 2 To m
'MsgBox r.Cells(k, 1).Address
If r.Cells(k, 1) = "" Then GoTo eexit
If r.Cells(k, 1).EntireRow.Hidden = False Then
If r.Cells(k, 1).Offset(0, 2) = 0 And _
r.Cells(k, 1).Offset(0, 3) = 0 And _
r.Cells(k, 1).Offset(0, 4) = 0 Then
r.Cells(k, 1).EntireRow.Hidden = True
End If
End If
Next k

GoTo eexit

continue:
 MsgBox "no such rows"
eexit:
End Sub

 

[venkat1926]

that is one of the good ways of sending data as it is easy to copy into excel sheet for writing macro and testing.

see your data in sheet 1 here

Excel Workbook
	A	B	C	D	E	F
1	room	101	*	*	*	*
2	*	*	*	*	*	*
3	*	*	*	*	*	*
4	*	*	*	*	*	*
5	*	*	*	*	*	*
6	Room	Type	Var A	Var B	Var C	Code
7	101	XX	1.2	0	0	P
8	100	DD	1.3	1.1	0	L
9	103	AA	0	0	0	L
10	105	GG	0	1	0	L
11	106	GG	1	1	1	P
12	121	KK	1.2	0	1.1	P
13	120	LL	1.1	1.1	0	L
14	101	YA	0	0	1.1	L
15	100	XX	0	1.2	0	P
16	104	DD	1.1	0	0	L
17	105	XX	1.2	0	0	L
18	101	JJ	0	0	1	P
19	101	XX	0	0	0	P
20	101	JJ	0	0	0	L
21	101	GG	0	0	0	P
Sheet1

the actual data is from 6th row. dont worry as long as there is no data between
A1 and the beginning of data column A the macro will find out in which row the data begins.; study the macro carefully otherwise the configuration should remain same. for e.g. the room number to be filtered should be in B1 only

the macro automatically undo the result of the macro and runs the balance codes.

for retesting just run the macro test.

Sub test()
Dim source As Range, r As Range, filt As Range
Dim x As Integer, j As Integer
Dim k As Integer, m As Integer, rbeg As Range

Worksheets("sheet1").Activate
ActiveSheet.AutoFilterMode = False
x = Range("B1").Value
Set source = Range("B1")
Set rbeg = Range("a1").End(xlDown)
'MsgBox rbeg.Address

Set r = Range(rbeg, rbeg.End(xlDown))
'MsgBox r.Address
m = rbeg.End(xlDown).Row
r.AutoFilter Field:=1, Criteria1:=x
If WorksheetFunction.CountA(r.Columns(1).SpecialCells(12)) = 1 Then
GoTo continue
j = WorksheetFunction.CountA(r.Columns(1).SpecialCells(12))

If j = 2 Then GoTo eexit

Set filt = r.SpecialCells(xlCellTypeVisible)


End If

For k = 2 To m
'MsgBox r.Cells(k, 1).Address
If r.Cells(k, 1) = "" Then GoTo eexit
If r.Cells(k, 1).EntireRow.Hidden = False Then
If r.Cells(k, 1).Offset(0, 2) = 0 And _
r.Cells(k, 1).Offset(0, 3) = 0 And _
r.Cells(k, 1).Offset(0, 4) = 0 Then
r.Cells(k, 1).EntireRow.Hidden = True
End If
End If
Next k

GoTo eexit

continue:
 MsgBox "no such rows"
eexit:
End Sub

 

[venkat1926]

sorry the message was duplicated by some glitch in my computer.administrator may please remove one of them.