VBA Hiding Unused Cells

[Dimitriy]

I was wondering if there is a more sophisticated way of hiding unused cells in a given range than what I have been doing so far:



If Sheets("Warehouse Personnel Form").Range("D10") = "" Then
Sheets("Warehouse Personnel Form").Rows("10:10").Select
Selection.EntireRow.Hidden = True
End If
If Sheets("Warehouse Personnel Form").Range("D11") = "" Then
Sheets("Warehouse Personnel Form").Rows("11:11").Select
Selection.EntireRow.Hidden = True
End If
If Sheets("Warehouse Personnel Form").Range("D12") = "" Then
Sheets("Warehouse Personnel Form").Rows("12:12").Select
Selection.EntireRow.Hidden = True
End If

and so on... I am interested in condensing my VBA code

Thank you.

[p45cal]

With Sheets("Warehouse Personnel Form")
For Each cll In .Range("D10:D12").cells
If cll.Value = "" Then cll.EntireRow.Hidden = True
End If
End With

or


For Each cll In Sheets("Warehouse Personnel Form").Range("D10:D12").cells
If cll.Value = "" Then cll.EntireRow.Hidden = True
End If

[Dimitriy]

Thanks for your help, but how do I define cells as "Cll" that you are using in your code? Or can you show me a way to work around that?

[p45cal]

You don't need to unless Option Explicit is active, in which case
Dim cll as range
at the top of the sub.

[Dimitriy]

Thanks, man. I've done that, but now it says that there is no IF for the END IF Block. Have you tried running the code yourself?

[mikerickson]

A special cells approach doesn't involve looping


Range("D10:D12").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True

[p45cal]

Thanks, man. I've done that, but now it says that there is no IF for the END IF Block. Have you tried running the code yourself?
Yes I have, before posting here.
Hey man, post your code here, better still, attach a small workbook so we can see where it fails.

[Artik]

A special cells approach doesn't involve loopingOK. But in large areas may be a problem. Look here http://support.microsoft.com/default.aspx?scid=kb;en-us;832293

Artik

[mdmackillop]

A slight slip in p45cal's code




Dim cll as Range

With Sheets("Warehouse Personnel Form")
For Each cll In .Range("D10:D12").cells
If cll.Value = "" Then cll.EntireRow.Hidden = True
Next
End With

'or

For Each cll In Sheets("Warehouse Personnel Form").Range("D10:D12").cells

If cll.Value = "" Then cll.EntireRow.Hidden = True
Next

[Dimitriy]

Thanks for all your help, I have decided to use the following code, but there is a problem:


Sheets("Warehouse Personnel Form").Select
Range("B63:B65").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True

The code does not work if all specified cells have some values in them. Is there a way to work around that by altering the code I am already using?

Thanks.

[Dimitriy]

Nevermind, looks like I got it to work with mdmackillop's code. Thanks.

[mdmackillop]

Sheets("Warehouse Personnel Form").Select
On Error Resume Next
Range("B63:B65").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
On Error GoTo 0