VBA code to hide blank rows
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 |
VBA code to hide blank rows
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 |
VBA code to hide blank rows
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 |
VBA code to hide blank rows
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 |
VBA code to hide blank rows
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 |
All times are GMT +1. The time now is 03:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com