VBA Code to Speed Up Hiding of Blank Cells/Rows in Specific Row Ranges

B

bearwires

Board Regular
Joined
Mar 25, 2008
Messages
57
Office Version
  1. 365
Platform
  1. Windows
Hi, I have put together this code to hide all blank cells in certain Row ranges across an array of worksheets.
It works ok, but it seems to take some time to complete and uses alot of computer resources to run.
Is it possible to use alternative VBA code to be more efficient and speed up the execution time and reduce the CPU/RAM usage?

VBA Code: 
Sub HideBlankRowsAllTabs()

sheetlist = Array("MS002", "MS003", "MS004", "MS005", "MS006", "MS007", "MS008", "MS009", "MS010", "MS011", "MS012")
For i = LBound(sheetlist) To UBound(sheetlist)
Worksheets(sheetlist(i)).Activate

For Each cell In Range("A330:A386")
  If cell.Value = "" Then cell.EntireRow.Hidden = True
    Next cell
   
For Each cell In Range("B293:B296")
  If cell.Value = "" Then cell.EntireRow.Hidden = True
    Next cell
 
For Each cell In Range("B232:B288")
  If cell.Value = "" Then cell.EntireRow.Hidden = True
    Next cell

For Each cell In Range("B197:B207")
  If cell.Value = "" Then cell.EntireRow.Hidden = True
    Next cell
 
For Each cell In Range("B176:B188")
  If cell.Value = "" Then cell.EntireRow.Hidden = True
    Next cell

Next
End Sub

I have a similar code to reverse the operation and unhide the rows in which I just changes the "True" to "False".

Also, is there syntax that can be added that autofits the rows/cells to the content when unhiding the rows?

Thanks
 
Sort by date Sort by votes
like this:
VBA Code: 
Sub HideBlankRowsAllTabs()

inarr = Range(Cells(1, 1), Cells(386, 2))
'For Each cell In Range("A330:A386")
For j = 386 To 330 Step -1
'  If cell.Value = "" Then cell.EntireRow.Hidden = True
    If inarr(j, 1) = "" Then Cells(j, 1).EntireRow.Hidden = True
    Next j
   
'For Each cell In Range("B293:B296")
   For j = 296 To 293 Step -1
 '  If cell.Value = "" Then cell.EntireRow.Hidden = True
    If inarr(j, 2) = "" Then Cells(j, 1).EntireRow.Hidden = True
    Next j
    'For Each cell In Range("B232:B288")
   For j = 288 To 232 Step -1
    If inarr(j, 2) = "" Then Cells(j, 1).EntireRow.Hidden = True
    Next j

   'For Each cell In Range("B197:B207")
   For j = 207 To 197 Step -1
    If inarr(j, 2) = "" Then Cells(j, 1).EntireRow.Hidden = True
    Next j
 
'  For Each cell In Range("B176:B188")
   For j = 188 To 176 Step -1
    If inarr(j, 2) = "" Then Cells(j, 1).EntireRow.Hidden = True
    Next j
 
Upvote 0

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
offthelip said:
like this:
VBA Code: 
Sub HideBlankRowsAllTabs()

inarr = Range(Cells(1, 1), Cells(386, 2))
'For Each cell In Range("A330:A386")
For j = 386 To 330 Step -1
'  If cell.Value = "" Then cell.EntireRow.Hidden = True
    If inarr(j, 1) = "" Then Cells(j, 1).EntireRow.Hidden = True
    Next j
 
'For Each cell In Range("B293:B296")
   For j = 296 To 293 Step -1
 '  If cell.Value = "" Then cell.EntireRow.Hidden = True
    If inarr(j, 2) = "" Then Cells(j, 1).EntireRow.Hidden = True
    Next j
    'For Each cell In Range("B232:B288")
   For j = 288 To 232 Step -1
    If inarr(j, 2) = "" Then Cells(j, 1).EntireRow.Hidden = True
    Next j

   'For Each cell In Range("B197:B207")
   For j = 207 To 197 Step -1
    If inarr(j, 2) = "" Then Cells(j, 1).EntireRow.Hidden = True
    Next j
 
'  For Each cell In Range("B176:B188")
   For j = 188 To 176 Step -1
    If inarr(j, 2) = "" Then Cells(j, 1).EntireRow.Hidden = True
    Next j
Click to expand...
It only works on the rows in the table I have in the document. Any other row in the specified ranges dont get hidden.

Ive amended the code with different cell references as the doc changed since you replied previously.
Both column A & Column B within those cell references are blank
Column A within those ranges have an IF functions whose true result is "". Only the IF statement falst result gives a value. The default is "".
Column B is either blank because no text typed in the cell, or because there is no selection made to a dropdown validation box in the cells.

Any ideas how to make it work and pick up all blank/empty rows and hide all of them?


VBA Code: 
Sub HideBlankRowsThisWorksheet()
'
' HideBlankRowsThisWorksheet Macro
'
inarr = Range(Cells(1, 1), Cells(404, 2))
'For Each cell In Range("A348:A404")
For j = 404 To 348 Step -1
'  If cell.Value = "" Then cell.EntireRow.Hidden = True
    If inarr(j, 1) = "" Then Cells(j, 1).EntireRow.Hidden = True
    Next j
   
'For Each cell In Range("B311:314")
   For j = 314 To 311 Step -1
 '  If cell.Value = "" Then cell.EntireRow.Hidden = True
    If inarr(j, 2) = "" Then Cells(j, 1).EntireRow.Hidden = True
    Next j
    'For Each cell In Range("B250:B306")
   For j = 250 To 306 Step -1
    If inarr(j, 2) = "" Then Cells(j, 1).EntireRow.Hidden = True
    Next j

   'For Each cell In Range("B215:B226")
   For j = 215 To 226 Step -1
    If inarr(j, 2) = "" Then Cells(j, 1).EntireRow.Hidden = True
    Next j
 
'  For Each cell In Range("B194:B206")
   For j = 194 To 206 Step -1
    If inarr(j, 2) = "" Then Cells(j, 1).EntireRow.Hidden = True
    Next j

'  For Each cell In Range("B132:B175")
   For j = 132 To 175 Step -1
    If inarr(j, 2) = "" Then Cells(j, 1).EntireRow.Hidden = True
    Next j
    
'  For Each cell In Range("B104:B125")
   For j = 104 To 125 Step -1
    If inarr(j, 2) = "" Then Cells(j, 1).EntireRow.Hidden = True
    Next j
    
   
End Sub
 
Upvote 0
I am not sure what you are trying to do?? Are you trying to only hide the rows where you have got a formula that gives a value which is blank? because that is quite possible to do with a simple modification to my code. Also I don't understand which columns you want to check for blanks and whether it changes depending on the row , which you initial code does.
Ths use of variant arrays ( which what I am doing) has a very simple correlation with cells addresses, provided you load the array starting in the top left cell. ( which my code does.)
inarr(3,5) is the same value as the cell row 3 column 5 i.e. E3 This make changethe cells which are addressed very straight forward
 
Upvote 0
offthelip said:
I am not sure what you are trying to do?? Are you trying to only hide the rows where you have got a formula that gives a value which is blank? because that is quite possible to do with a simple modification to my code. Also I don't understand which columns you want to check for banks and whether it change depending on the row , which you initial code does.
Ths use of variant arrays ( which what I am doing) has a very simple correlation with cells addresses, provided you load the array starting in the top left cell. ( which my code does.)
inarr(3,5) is the same value as the cell row 3 column 5 i.e. E3 This make changethe cells which are addressed very straight forward
Click to expand...

This is a short explanation of existing layout and what needs to happen at each specified range of cells/rows. Hope it makes sense:


Range("A348:A404") is a table with all IF formulae. If any row is blank, I need it hidden. Column B only has text if IF statement false value is realised. Columns B,C,D,E & F within this range is also blank if column A is blank but populates when Column A is populated. It pulls text from a separate worksheet.

Range("B311:314") is validation drop down boxes. If any row is blank, I need it hidden. Column A cell is also blank if Column B cell is blank.

Range("B250:B306") is a list with each cell being IF formulae. If any row is blank, I need it hidden. Column A cell is also blank if Column B cell is blank. Nothing in column B, nothing in column A. If B becomes some text value as a result of a dropdown list on another tab, it inserts a bullet point in column A using IF function.

Range("B215:B226") is validation drop down boxes. If any row is blank, I need it hidden. Column A cell is also blank if Column B cell is blank.

Range("B194:B206") is validation drop down boxes. If any row is blank, I need it hidden. Column A cell is also blank if Column B cell is blank.

Range("B132:B175") is empty unless something is typed into each cell in column B. I need it to be like this, if something gets typed, only hide the remaining blank rows. Column A cell is also blank if Column B cell is blank. The action of typing text inserts a bullet point in column A using IF function.

Range("B104:B125") is empty unless something is typed into each cell in column B. I need it to be like this, if something gets typed, only hide the remaining blank rows. Column A cell is also blank if Column B cell is blank. The action of typing text inserts a bullet point in column A using IF function.
 
Upvote 0
you state : "If any row is blank" what exactly do you mean by this which column need to be tested to determine this
 
Upvote 0
does this do what you want??
VBA Code: 
Sub HideBlankRowsAllTabs()

inarr = Range(Cells(1, 1), Cells(404, 2))

For j = 404 To 348 Step -1
'  If cell.Value = "" Then cell.EntireRow.Hidden = True
    If inarr(j, 1) = "" Then Cells(j, 1).EntireRow.Hidden = True
    Next j
 

   For j = 314 To 311 Step -1
 '  If cell.Value = "" Then cell.EntireRow.Hidden = True
    If inarr(j, 2) = "" Then Cells(j, 1).EntireRow.Hidden = True
    Next j

   For j = 306 To 250 Step -1
    If inarr(j, 2) = "" Then Cells(j, 1).EntireRow.Hidden = True
    Next j


   For j = 226 To 215 Step -1
    If inarr(j, 2) = "" Then Cells(j, 1).EntireRow.Hidden = True
    Next j
 

   For j = 206 To 194 Step -1
    If inarr(j, 2) = "" Then Cells(j, 1).EntireRow.Hidden = True
    Next j

   For j = 175 To 132 Step -1
    If inarr(j, 2) = "" Then Cells(j, 1).EntireRow.Hidden = True
    Next j

   For j = 125 To 104 Step -1
    If inarr(j, 2) = "" Then Cells(j, 1).EntireRow.Hidden = True
    Next j

End Sub
 
Upvote 0
offthelip said:
you state : "If any row is blank" what exactly do you mean by this which column need to be tested to determine this
Click to expand...
The rows shown in the ranges provided. Any other row, outside these ranges, needs to be ingored.

Either Column A or Column B can be tested as the related cells in both these columns are either blank ("") or has a text string value.
For simplicity, maybe stick to column B throughout.

Each row either has a value (text string) or it doesnt because the IF function result hasn't been satisfied.

So the cell value in Column A & column B within the row ranges given are either "" or a text string value.

Does that make sense?
 
Upvote 0
offthelip said:
Good, I think the code in post #16 should do that for you
Click to expand...
Yes it does work, thanks mate :)

Is there any VBA code that will overcome the merged cell autofit issue that excel has?
I have some rows which fit within the row height but there are some rows which have merged cells & text wrapped.
Can some additional code be included in what you've written to auto-fit all row heights in the entire worksheet, including the merged cell with wrapped text, but still keep the cells that has been hidden, hidden?
 
Upvote 0
Can I suggest starting a new thread for that query since it has nothing to do with the title on this thread, and if a solution is found it can help others. However my only suggestion is avoid merged cells entirely they just create problems and usually there are other ways of achieving the same result., i.e., format "centre across selection". I NEVER use them
Did the final code run significantly faster??
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Hide Row Based on cell value which more than 45 days
2
Replies
12
Views
714
JayHo
J
S
  • Question Question
Can’t hide column using VBA
Replies
3
Views
269
Joe4
Joe4
N
  • Question Question
Hiding/showing rows between two values
Replies
1
Views
435
Joe4
Joe4
C
  • Solved
Speed up VBA Code (Hide Blank cells based on specific cell)
Replies
4
Views
605
CamC
C
itsgrady
  • Solved
I would like to unhide the row after printing
Replies
4
Views
448
itsgrady
itsgrady

Forum statistics

Threads
1,223,896
Messages
6,175,264
Members
452,627
Latest member
KitkatToby

Share this page

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