Hide rows with no data in specific columns

Question

Hello all,

I work in a callcenter as team coach. Every week we get an export from our customer with the stats of our agents from the previous week. I'm trying to make my work as easy as possible and let Excel do most of the work.

My problem at this moment is that if an agent is on leave/holiday specific columns will remain empty. In this case columns 'C' to 'N'. When these columns in a specific row are empty I want to hide the complete row.

However, it's important to know the cells will appear empty, but actually they will contain a formula (mostly VLOOKUP's). If no result is returned by this formula the cell will appear empty. And second, column 'B' will never be empty as it contains the agent's name. Since I'm not that familiar with macros I'm kind of stuck.

Is there anyone who can help me?

If it's not to much trouble, can you also give a short explanation about what the different sections of the formuly do? This way I maybe can adapt this formula when I need it in other files.

Thanks in advance guys!

Niels

Answer 1

Hi,

Try this version. It has an inputbox with a default of 23 for the last row which you can change.

Sub hide_rows()

'Declare variables

Dim Lastrow As Long, x As Long

' Find the last used row on the sheet

Lastrow = Application.InputBox("Enter last row number", "Row", 23, Type:=2)

'Loop to look at every row

For x = 4 To Lastrow

    'test every cell in the row column C to N to see if they are blank

    If WorksheetFunction.CountBlank(Cells(x, "C").Resize(, 12)) = 12 Then

    'if there are 12 enply cells (C-N) then hide the row

    Rows(x).Hidden = True

    End If

Next

End Sub

Answer 2

Thanks Mike!

This already helped me a lot.

One more question though...

Is it possible to only apply this macro in, for example, row range 4 to 22?

And say if I want to add a row after I set the macro (for example, new team member), does this update the macro automatically to row 4 - 23? Or do I have to manually adjust it?

Thanks a lot!

Answer 3

Hi,

Try this code. See the comments for what each line is doing.

Sub hide_rows()

'Declare variables

Dim Lastrow As Long, x As Long

' Find the last used row on the sheet

Lastrow = Cells.Find(What:="*", After:=[A1], _

SearchOrder:=xlByRows, _

SearchDirection:=xlPrevious).Row

'Loop to look at every row

For x = 1 To Lastrow

    'test every cell in the row column C to N to see if they are blank

    If WorksheetFunction.CountBlank(Cells(x, "C").Resize(, 12)) = 12 Then

    'if there are 12 enply cells (C-N) then hide the row

    Rows(x).Hidden = True

    End If

Next

End Sub