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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
try this, I have used a varaint array to reduce the number of access to the workhseet in the loop:
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
Worksheets(i + 1).Activate
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

Next
End Sub
 
Upvote 0
How about:

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
'
        On Error Resume Next
'
        Range("A330:A386").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
        Range("B293:B296").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
        Range("B232:B288").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
        Range("B197:B207").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
        Range("B176:B188").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
'
        On Error GoTo 0
    Next
End Sub
 
Upvote 0
johnnyL said:
How about:

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
'
        On Error Resume Next
'
        Range("A330:A386").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
        Range("B293:B296").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
        Range("B232:B288").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
        Range("B197:B207").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
        Range("B176:B188").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
'
        On Error GoTo 0
    Next
End Sub
Click to expand...
Thanks Johnny, this works perfectly.

How do I unhide all the hidden rows?
Ive tried changing True to False but that doesnt seem to unhide all the rows which has formulae in them.
It works for the blank rows and the rows with validation dropdown boxes, but if the referenced cell has a formulae, it doesnt unhide those rows.
Any ideas?
 
Last edited:
Upvote 0
offthelip said:
try this, I have used a varaint array to reduce the number of access to the workhseet in the loop:
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
Worksheets(i + 1).Activate
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

Next
End Sub
Click to expand...
Thanks for your help but this seems to not limit itself to the specified worksheet tab array and does all the tabs in the workbook.
 
Upvote 0
johnnyL said:
Did you try:

VBA Code: 
Cells.EntireRow.Hidden = False
Click to expand...
That seems to work, thanks Johnny.

The previous code to hide all the cells worked perfectly earlier today but when I try it now it doesnt work, it doesnt hide ALL the blank cells with formulae in them. It works on the other cells which is just text. I havent made any changes to the document. Do you know what the issue could be?
I have tried changing the code to:
VBA Code: 
cells.EntireRow.Hidden = True
but this didnt seem to do anything.
 
Upvote 0
bearwires said:
Thanks Johnny, this works perfectly.

How do I unhide all the hidden rows?
Ive tried changing True to False but that doesnt seem to unhide all the rows which has formulae in them.
It works for the blank rows and the rows with validation dropdown boxes, but if the referenced cell has a formulae, it doesnt unhide those rows.
Any ideas?
Click to expand...

Hi Johnny,

Ive had to amend the macro to only operate on the active sheet rather than the array of worksheets.
This is the syntax mod I made:

VBA Code: 
Sub HideBlankRowsThisWorksheet()
'
Dim ws As Worksheet
Set ws = ActiveSheet

        On Error Resume Next
       
        ws.Range("A348:A404").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True    'RA Table
        ws.Range("B311:B314").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True    'COSHH
        ws.Range("B250:B306").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True    'RA List
        ws.Range("B215:B226").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True    'Materials
        ws.Range("B194:B206").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True    'P&E
        ws.Range("B132:B175").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True    'Sequence of Works 2
        ws.Range("B104:B125").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True    'Sequence of Works 1
'
        On Error GoTo 0

End Sub


Annoyingly, this doesn't seem to work correctly even though the syntax is showing as correct in VBA editor.

Any referenced cells which has a blank value due to an IF formula true result of "", will not hide, it just hides the entire range specified.
These are the rows / ranges which have the issue:

VBA Code: 
ws.Range("A348:A404").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True    'RA Table
ws.Range("B250:B306").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True    'RA List

Any referend cells which are either blank for manual typing or blank with validation dropdown boxes unselected work fine.

If I revert to my original code below, it works on all referenced cells/rows, but its painfully slow to run:

VBA Code: 
For Each cell In Range("A348:A404")
  If cell.Value = "" Then cell.EntireRow.Hidden = True
    Next cell
      
For Each cell In Range("B311:B314")
  If cell.Value = "" Then cell.EntireRow.Hidden = True
    Next cell
    
For Each cell In Range("B250:B306")
  If cell.Value = "" Then cell.EntireRow.Hidden = True
    Next cell

For Each cell In Range("B215:B226")
  If cell.Value = "" Then cell.EntireRow.Hidden = True
    Next cell
    
For Each cell In Range("B194:B206")
  If cell.Value = "" Then cell.EntireRow.Hidden = True
    Next cell

For Each cell In Range("B132:B175")
  If cell.Value = "" Then cell.EntireRow.Hidden = True
    Next cell
    
For Each cell In Range("B104:B125")
  If cell.Value = "" Then cell.EntireRow.Hidden = True
    Next cell
    
Next



Any idea what maybe causing the issue?


Thanks
 
Upvote 0
Did you ever try my code from post #2 which is a slight modification of your code and should work the same way but work a bit faster
 
Upvote 0
offthelip said:
Did you ever try my code from post #2 which is a slight modification of your code and should work the same way but work a bit faster
Click to expand...
This one?

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
Worksheets(i + 1).Activate
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

Next
End Sub


I am using a macro button on each worksheet rather than doing all specified tabs together as it give flexibility using my document.#

How do I amend you code to work for the active sheet only?

Thanks
 
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,888
Messages
6,175,208
Members
452,618
Latest member
Tam84

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