Hi all ..
Is there a way to hide rows within a range that do not have data in the B column of that row without using a macro button?
The range is row 5-60 .. if data is not in column B of that row, can that row be hidden?
If so, is this code that goes in the Sheet? or in a module?
HELP :?
Re: Hiding Empty Rows Without Macro Button
Blue,
Have you thought of filtering your data range (Data > Filter > Autofilter) and then selecting (nonblanks) from the column B drop down arrow?
If this is too simplistic, then use the following code:
Sub Delete_Blank_Rows()
Dim i As Integer
For i = 5 To 60
If Cells(i, 2).Value = "" Then
Rows(i).Hidden = True
End If
Next i
End SubIf it's the bit that you don't want to have a button on the worksheet that you are more concerned about...basically you need to have something that will kick off the code so you could assign a keyboard shortcut to your macro instead. There's quite a few ways you could initiate the running of the code (it just depends on your preference).
Hope this helps
B :roll: :roll:
Re: Hiding Empty Rows Without Macro Button
I guess I am just after the easiest way to hide them without the officer who is entering data in the worksheet having to do anything .. as it's not me that will be entering the data.
Can the code you suggested be auto run when the file is opened?
Basically, two officer have a file to input data into .. both officers enter data into rows 5-60 in their own file .. then a master database if you like collects the data into another file. So officer 1's data will appear in rows 5-60 in the master file and officer 2's data will appear in 61-116.
I am after the master file only to display the rows that have data in them.
So upon opening the master file, all rows that are blank will be hidden.
Does this make sense?
[email protected]
Thanks for you help Boo :thanx:
Re: Hiding Empty Rows Without Macro Button
Then again .. as the officer enters data into a row that IS blank .. can that line then appear in the master file without having to close and reopen?
Re: Hiding Empty Rows Without Macro Button
Blue,
Sorry for the delay in replying.
To answer your questions:
1. The code can be autorun by inserting it into the Workbook_Open() event
2. Re: the master file (Spreadsheet) being updated automatically when an officer enters data in a row that IS blank - technically this can be achieved but the actual process doesn't make sense - why would you want to look at the master spreadsheet when someone is in the middle of entering data - I suggest that this would be slightly risky?
Don’t have an account yet? Register yourself now and be a part of our community!