Select NON blank cells
Went ahead and modified it to do Rows if that's what you want, also cleaned
up the Columns version - amazed that it worked! But it managed to ignore my
error in coding somehow. One of these two should do what you want:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
'hide empty cells on same row that double-click was in
Dim thisRow As String
If Target.Cells.Count 1 Then
Exit Sub ' only on single cell select
End If
Application.EnableEvents = False
Cancel = True
thisRow = Target.Row & ":" & Target.Row
Rows(thisRow).Select
Selection.EntireColumn.Hidden = False
Range(thisRow).SpecialCells(xlCellTypeBlanks).Sele ct
Selection.EntireColumn.Hidden = True
Application.EnableEvents = True
End Sub
or for columns (corrected code)
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
'hide empty cells in same COLUMN that double-click was in
Dim thisColumn As String
If Target.Cells.Count 1 Then
Exit Sub ' only on single cell select
End If
Application.EnableEvents = False
Cancel = True
thisColumn = Left(Target.Address, InStr(2, Target.Address, "$") - 1) _
& ":" & Left(Target.Address, InStr(2, Target.Address, "$") - 1)
Columns(thisColumn).Select
Selection.EntireRow.Hidden = False
Range(thisColumn).SpecialCells(xlCellTypeBlanks).S elect
Selection.EntireRow.Hidden = True
Application.EnableEvents = True
End Sub
"pcor" wrote:
I want to click on a cell and have only the cells that contain data in that
colum to be visible.(Lets assume I click in cell b5, then I want only the non
blank cells in col 5 to be visible)(ie the same as using "SELECT all NON
BLANK cells)
But I want a macro to do this. Any help will be appreciated
Thanks
Ian
|