[UB]

Hi
The data in my sheet is in the below mentioned format.

Column B
1 <Blank
2 <Blank
3 <Blank
4 <Blank
5 Total
6 2
7 4
8 6
9 <Blank
10 Total 12
I need to write a VBA code to hide rows that are blank and subsquently the
row that has field "Total"
Ex: Rows 1,2,3,4 are blank , so their "Total"-Row#5 is blank, so I need to
hide all these 5 rows.
In rows 6 to 10, row#9 is blank. So in this range, I need to hide only row # 9
I need to write a code that will satify both condition in a given range.

Please advise
Thanks

[Bernie Deitrick]

Select your table, then use Data / Filter / Autofilter, then select Non-Blanks from the dropdown at
the top of the column.

In code, that would be, possibly. though you may want more filtering that allowed by this:

Range("B:B").AutoFilter Field:=1, Criteria1:="<"

HTH,
Bernie
MS Excel MVP


"ub" wrote in message
...
Hi
The data in my sheet is in the below mentioned format.

Column B
1 <Blank
2 <Blank
3 <Blank
4 <Blank
5 Total
6 2
7 4
8 6
9 <Blank
10 Total 12
I need to write a VBA code to hide rows that are blank and subsquently the
row that has field "Total"
Ex: Rows 1,2,3,4 are blank , so their "Total"-Row#5 is blank, so I need to
hide all these 5 rows.
In rows 6 to 10, row#9 is blank. So in this range, I need to hide only row # 9
I need to write a code that will satify both condition in a given range.

Please advise
Thanks

[Pete_UK]

You can do this by applying autofilter to column B and select Non-
blanks from the filter pull-down. It's not dynamic, though, so if
something changes to make one of the blank cells into a value then you
will need to re-apply the filter condition.

Hope this helps.

Pete

On Jul 31, 1:10*pm, ub wrote:
Hi
The data in my sheet is in the below mentioned format.

* * * * Column B
1 * * * <Blank
2 * * * <Blank
3 * * * <Blank
4 * * * <Blank
5 * * *Total * *
6 * * * * * 2
7 * * * * * 4
8 * * * * * 6
9 * * * * * * * <Blank *
10 * Total * * * * 12
I need to write a VBA code to hide rows that are blank and subsquently the
row that has field "Total"
Ex: Rows 1,2,3,4 are blank , so their "Total"-Row#5 is blank, so I need to
hide all these 5 rows.
In rows 6 to 10, row#9 is blank. So in this range, I need to hide only row # 9
I need to write a code that will satify both condition in a given range.

Please advise
Thanks

[UB]

Thanks Bernie & Pete_UK

It works

Regards

"Bernie Deitrick" wrote:

Select your table, then use Data / Filter / Autofilter, then select Non-Blanks from the dropdown at
the top of the column.

In code, that would be, possibly. though you may want more filtering that allowed by this:

Range("B:B").AutoFilter Field:=1, Criteria1:="<"

HTH,
Bernie
MS Excel MVP


"ub" wrote in message
...
Hi
The data in my sheet is in the below mentioned format.

Column B
1 <Blank
2 <Blank
3 <Blank
4 <Blank
5 Total
6 2
7 4
8 6
9 <Blank
10 Total 12
I need to write a VBA code to hide rows that are blank and subsquently the
row that has field "Total"
Ex: Rows 1,2,3,4 are blank , so their "Total"-Row#5 is blank, so I need to
hide all these 5 rows.
In rows 6 to 10, row#9 is blank. So in this range, I need to hide only row # 9
I need to write a code that will satify both condition in a given range.

Please advise
Thanks

[Stingz]

Hi,

Assuming is your below table:
A B
1 S.No Row
2 A 21
3 B 54
4 C
5 D 75
6 Total 150
7 A 21
8 B 54
9 C 45
10 D 120
11 Total 240
12 A
13 B
14 C
15 D
16 Total 0

Have a function on "M1" to pick the count of non blanks so that the range
for checking the condition is dynamic.. =COUNTA(A1:A29)

on Module 1 write the below code:

Sub Macro1()
X = Range("M1").Value
For I = 1 To X
Range("B" & I).Select
Y = Range("B" & I).Value
z = Range("A" & I).Value
If Y = "" Or (Y = 0 And z = "Total") Then
Rows(I & ":" & I).Select
Selection.EntireRow.Hidden = True
End If
Next I
End Sub

Hope this has solved your query.
Regards,
Stingz

"ub" wrote:

Hi
The data in my sheet is in the below mentioned format.

Column B
1 <Blank
2 <Blank
3 <Blank
4 <Blank
5 Total
6 2
7 4
8 6
9 <Blank
10 Total 12
I need to write a VBA code to hide rows that are blank and subsquently the
row that has field "Total"
Ex: Rows 1,2,3,4 are blank , so their "Total"-Row#5 is blank, so I need to
hide all these 5 rows.
In rows 6 to 10, row#9 is blank. So in this range, I need to hide only row # 9
I need to write a code that will satify both condition in a given range.

Please advise
Thanks