Hide rows next to first blank cell in a specific column

girapas

Board Regular
Joined
Apr 20, 2004
Messages
150
In the sheet1 all the cells of columns C to H are populated with data by a formula that get data from another sheet.
The cell of the column G that corresponds to the current month is always blank.
Which is the code to hide all the rows next to the row of that blank cell (e.g. rows 12 to 25 in sheet1)?
Thanks in advance


Book1
ABCDEFGH
1yearmonthdata1data2data3data4data5
212013Jan705888933920673
32Feb307562535920440
43Mar776850901784814
54Apr298706340242396
65May416986548863738
76Jun805913792413304
87Jul835580364591241
98Aug418626220240744
109Sep255355605468928
1110Oct25192015
1211Noe
1312Dec
1412014Jan
152Feb
163Mar
174Apr
185May
196Jun
207Jul
218Aug
229Sep
2310Oct
2411Noe
2512Dec
Sheet1
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Does this macro do what you want...

Code:
Sub HideRowsAfterCurrentMonth()
  Dim LastRow As Long
  LastRow = Evaluate("MAX(ROW(G1:G65535)*(G1:G65535<>""""))") + 2
  Range(Cells(LastRow, "G"), Cells(Rows.Count, "A").End(xlUp)).EntireRow.Hidden = True
End Sub
 
Upvote 0
Thank you for your help
While I am guessing you will never have enough data for the following change to matter, I have changed the 65535 hard-coded values (which are really XL2003 limits) and replaced them with generalize limits which will automatically use the number of rows in whatever version of Excel you are using...

Code:
Sub HideRowsAfterCurrentMonth()
  Dim LastRow As Long
  LastRow = Evaluate("MAX(ROW(G1:G" & Rows.Count - 1 & ")*(G1:G" & Rows.Count - 1 & "<>""""))") + 2
  Range(Cells(LastRow, "G"), Cells(Rows.Count, "A").End(xlUp)).EntireRow.Hidden = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top