PhilW_34
Board Regular
- Joined
- Jan 4, 2007
- Messages
- 128
- Office Version
- 365
- Platform
- Windows
Hello,
I'm updating a file I've used for a long time. In this file, I'd manually enter data in cells A5:A16. If I only entered in one cell in that range, the rest were blank. However, I've decided to upgrade the file and now I'm using formulas to populate data in A5=A16. My .specialCells(xlCellTypeBlanks) is no longer working on the cells that only look blank because the formula result is "". Other code below hides corresponding rows below my original range. I still need to do that.
Ultimately, how can I revise xlCellTypeBlanks to something like = ""? THanks.
Phil
I'm updating a file I've used for a long time. In this file, I'd manually enter data in cells A5:A16. If I only entered in one cell in that range, the rest were blank. However, I've decided to upgrade the file and now I'm using formulas to populate data in A5=A16. My .specialCells(xlCellTypeBlanks) is no longer working on the cells that only look blank because the formula result is "". Other code below hides corresponding rows below my original range. I still need to do that.
Ultimately, how can I revise xlCellTypeBlanks to something like = ""? THanks.
Phil
Code:
Sub Hide_Rows()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
With ActiveSheet
On Error Resume Next
Set rngRowsToHide = .Range("A5:A16").SpecialCells(xlCellTypeBlanks) '<- Need to fix this to make it work right again.
Set rngColsToHide = .Range("F4:X4").SpecialCells(xlCellTypeBlanks) '<- Need to fix this to make it work right again.
On Error GoTo 0
End With
If Not rngRowsToHide Is Nothing Then
Set rngRowsToHide = Union(rngRowsToHide, _
rngRowsToHide.Offset(23), _
rngRowsToHide.Offset(39), _
rngRowsToHide.Offset(55), _
rngRowsToHide.Offset(71))
rngRowsToHide.EntireRow.Hidden = True
End If
On Error Resume Next
If Not rngColsToHide Is Nothing Then
Set rngColsToHide = Union(rngColsToHide, _
rngColsToHide.Offset(0, 24))
rngColsToHide.EntireColumn.Hidden = True
End If
Columns("Z:AF").EntireColumn.Hidden = False
On Error Resume Next
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub