Macro command button to hide empty rows

I have a master worksheet which will copy data from several pages on a if lookup formula, if it fails it puts "" in instead.

Can someone help me with the code for a macro that will hide each row if the cell in column A contains "".

The macro will be attached to a command button, so will either need to have a 1 click to hide, second click to unhide. Or have 2 buttons.

I'm not sure if I've explained myself, if not please let me know and I will try to clarify.

A couple of comments. (1) Since you are already coding with VBA, why not use VBA to extract your information rather than use Lookups that will have blank rows? (2) If you do need the formulas, can you post a sample of your workbook so we can more easily code this for you?

Honestly, as I've had a lot less experience with macros, so I tend to use formula when I can.

Add a togglebutton to your sheet, and use the code in the Click event

Private Sub ToggleButton1_Click()
     Dim lrow As Long
     Dim r As Range
     
     lrow = Cells(Rows.Count, 1).End(xlUp).Row
     Set r = Range("A3:A" & lrow)
     
     Application.ScreenUpdating = False
     
     For Each cell In r
          If cell.Value = "" Then
               cell.EntireRow.Hidden = Sheet1.ToggleButton1.Value
          End If
     Next cell
     
     Application.ScreenUpdating = True

End Sub

I've got this error message :Serror message.jpg

Djauncey,

The code SHOULD work, and in fact did work on a test sheet that I worked with. If you are having problems, can you possibly post a copy of the workbook?

Also, I noticed an error in the code. In the code I wrote, I used the line:

lrow = Cells(Rows.Count, 1).End(xlUp).Row

to determine the last row of your range. This allows us to use a dynamic range and NOT use a fixed range.

That said, the following code checks expands the range to the used range within the worksheet:

     lrow = Cells(Rows.Count, 1).End(xlUp).Row
     Set r = Range("A3:A" & lrow)

THe range is now A3 to A and the last row. In your code, you used

     lrow = Cells(Rows.Count, 1).End(xlUp).Row
     Set r = Range("A3:A20000" & lrow)

Essentially, you used BOTH a fixed range AND the variable, which MAY be the cause of the problems. Amend that and see if it helps. If not, then post a copy of the book.

Hi BigBas, thanks for the help so far. I've got it working which is good.

1 final question, is it possible to have it set up so the first click hides those cells and then the second click shows them again?