Hide blank rows on all worksheets on an Excel workbook

Question

I have a workbook with about 25 worksheets. And I want a macro to hide any rows (from 5 to 33) if there is nothing (number or text) in the column A of that row.

Can someone help please?

I have seen similar ones deleting blank rows ect. But I am not smart enough to change those to fit. If you could give me the code I can copy it on to my file (VBA).

Please help... Thanks

Sub Hiderow() 
    Application.ScreenUpdating = False 
    Dim s As String 

    For i = 1 To Range("A5:A33").Count 
        s = i & ":" & i 
          If IsEmpty(Cells(i, 1).Value) Then Rows(s).EntireRow.Hidden = True 
    Next 

    Application.ScreenUpdating = True 
End Sub

If you have one to delete then change `.Delete` to .`Hidden = True`. Share the code you have, it's likely a very quick change (*something exactly like, or close to, the above*) — urdearboy, Feb 10 '21 at 16:30
@urdearboy and it is why many will answer questions they shouldn't. and why we keep getting questions that request code. — Scott Craner, Feb 10 '21 at 16:31
@LindaCarter - I misread. If you found code to delete then you should take that and try to make it fit your specific needs. You will rarely find code to do exactly what you want so finding something close (like deleting rows) is a great place to start. Take a stab and updating that code to do what you need and then come back when you have a specific question regarding the code — urdearboy, Feb 10 '21 at 16:32
I have found this. Sub Hiderow() Application.ScreenUpdating = False Dim s As String For i = 1 To Range("A5:A33").Count s = i & ":" & i If IsEmpty(Cells(i, 1).Value) Then Rows(s).EntireRow.Hidden = True End If Next Application.ScreenUpdating = True End Sub — Linda Carter, Feb 10 '21 at 16:34
@LindaCarter add that code to your question and **explain what issues you are having with the code** :) From there, we are happy to help you adjust — urdearboy, Feb 10 '21 at 16:36
But it leaves a few rows 30,31&32 even though they don't have anything on column A. Also this works only on one sheet. How to get it to work on all sheets in one go? Sorry if i am asking too much. — Linda Carter, Feb 10 '21 at 16:36
@LindaCarter here is a [solution](https://stackoverflow.com/a/25953980/6706419) that shows you how to loop through many worksheets. — urdearboy, Feb 10 '21 at 16:38
Thanks urdearboy. I find that code very complex. I have no knowledge over coding & not brave enough to combine two. I am simply recording macros on Excel to do new things & occasionally doing simple changes to fit. It looks like I need to keep Googling. Thanks for your help. You are very kind. — Linda Carter, Feb 10 '21 at 16:45
@LindaCarter you will need to search on how to loop worksheets, then use that code and marry it with this code. Also in the code try: `For i = 5 to 33` — Scott Craner, Feb 10 '21 at 16:46
Yay...That worked Scott. So it now nicely hides the blank rows on that sheet. Sorry. The next question is can I replace the word hide & hidden with unhide & unhidden to make a code to unhide those rows again? Thanks A LOT. — Linda Carter, Feb 10 '21 at 16:52
@LindaCarter - you have to start somewhere. This is exactly how I learned VBA like 3 years ago. Trying and failing and then asking questions here. Rinse and repeat for about a year :P — urdearboy, Feb 10 '21 at 16:54
to unhide just loop the sheets the same way but instead of looping the rows just do: `.Rows("5:33").Hidden = False` and it will unhide everything. — Scott Craner, Feb 10 '21 at 16:56

score 1 · Answer 1 · answered Feb 10 '21 at 16:52

Loop each sheet then loop rows 5 to 33 in each worksheet.

Sub Hiderow()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    'Loop each worksheet
    For Each ws In ThisWorkbook.Worksheets
        'make sure the ranges refer to the correct sheet
        With ws
            'Loop the rows
            For i = 5 To 33
                'Set hidden status based on whether there is a visible value in column A
                .Rows(i).Hidden = .Cells(i, 1) = ""
            Next
        End With
    Next ws
    Application.ScreenUpdating = True
End Sub

Hide blank rows on all worksheets on an Excel workbook

1 Answers1