PDA

View Full Version : Application Object Error- Range(Cells(



YellowLabPro
06-25-2007, 06:07 AM
Good morning All,
My Sub is failing. I thought by telling it the range and then Cells, I could avoid looping- Is what I am trying to do possible, or do I need to loop?


Sub Hide()
Dim Ws As Worksheet
Set Ws = Workbooks("Sheet References.xls").Worksheets("Sheet1")
If Ws.Range(Cells("A:AI" = "Blank")) Then
Ws.Range(Cells("A:AI")).EntireColumns.Hidden = True
End If
End Sub


Thanks,

Doug

mikerickson
06-25-2007, 07:07 AM
The SpecialCells property will do what you want. The On Error is in case no cells are blank.

Sub Hide()
Dim Ws As Worksheet
Set Ws = Workbooks("Sheet References.xls").Worksheets("Sheet1")
With Ws
On Error Resume Next
.Cells.SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeBlanks).Entir eColumn.Hidden = True
On Error GoTo 0
End If
End Sub

YellowLabPro
06-25-2007, 12:20 PM
Thanks Mike,
I just got back and ran your code. Rather funny on my part, I knew there was a problem when I copied and used it, but it took me a couple of minutes to figure it out. I was up all night doing a photoshoot so the brain is really groggy today.
"Blank" is a term I am basing my criteria on to hide the column, not acutal blank cells. If the term "Blank" is anywhere in column then hide the entire column. I was trying to cheat and not have to loop-- don't know if this is possible.

mikerickson
06-25-2007, 12:28 PM
If you are looking for cells containing "blank", you will have to use a loop.
If the loop uses .Find it will be shorter than checking every cell in the range.