If Coloum E, F, G, H are Blank need to Hide those entire Rows

[sur]

Hello All

I have a data where in i need to Hide the entire rows if the Coloum E, F, G, H are Blank or Zero.

Please help me with the VBA code

Thanks Regards

 

[venkat1926]

is the condition that any of column E,F,G,H blank or all of them

 

[sur]

yes, if all the coloumn E, F, G, H are blank or else zero it should hide the entire row

 

[venkat1926]

the sample data is like this in sheet1

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	hdng1	hdng2	hdng3	hdng4	hdng5	hdng6	hdng7	hdng8	hdng9	hdng10
2	2	39	83	10	75	56	84	40	5	72
3	46	7	57	31	34	84	1	85	98	37
4	45	11	66	38	85	32	12	38	98	36
5	40	14	78	31			0		55	13
6	93	55	30	82	87	25	82	9	39	60
7	90	35	19	74	27	89	67	81	60	21
8	41	70	37	90	1	45	12	34	68	55
9	66	24	65	28				0	91	93
10	30	14	48	59	9	94	96	55	94	52
11	87	95	34	96	12	96	81	59	33	65
12	48	1	69	99	42	9	49	39	3	71
13	62	33	27	70					73	26
14	36	10	87	63	84	89	98	93	5	27
Sheet1

now try the macro test on this (undo will remove the hiding of rows and get back the original data)-to be on safe side keep a copy of sheet in sheet 2.


activate sheet1 and run "test"

Sub test()
Dim r As Range, filt As Range, j As Integer
Set r = Range("A1").CurrentRegion
For j = 5 To 8
 r.AutoFilter field:=j, Criteria1:=" ", Operator:=xlOr, Criteria2:=0
Next j
Set filt = r.Offset(1, 0).Resize(r.Rows.Count - 1, r.Columns.Count).SpecialCells(xlCellTypeVisible)
'MsgBox filt.Address

r.AutoFilter
filt.EntireRow.Hidden = True

End Sub

Sub undo()
Dim r As Range
Set r = Range("a1").CurrentRegion
r.Rows.Hidden = False
End Sub

 

[sur]

Thanks for the Reply

The data i have is 3 to 4 workbook merged into one excel workbook

so there is gap from one current region to another.

please help me to select all the rows in workbook and run the macro.

 

[venkat1926]

use this statement and try

set r=activesheet.usedrange
add a msgbox
msgbox r.addres
(for checking)

the idea is somehow find the address of "lastcell"