Select NON blank cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Before doing this, need to know exactly what you want to show.

You said "..click in cell b5, then I want only the non blank cells in col
5..."

When you are in cell B5, the COLUMN you are in is B (or column 2) and the
ROW you are in is row 5.

Do you want the column or the row information to remain visible?

By the way, you probably don't want this to happen when you simply choose a
cell, probably be best to require a double-click in a cell to make it happen.

The VBA code will need to be associated with a Worksheet's event (as
_BeforeDoubleClick) - do you know how to put that kind of code into your
workbook or will you need instructions?

On the off chance that you meant COLUMN instead of ROW, and that you can get
the code into the proper place, heres code that will do it for the column.
And instructions for putting it in the worksheet code can be found here:
http://www.jlathamsite.com/Teach/WorksheetCode.htm

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
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 & ":" & thisColumn).SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Hidden = True
Application.EnableEvents = True
End Sub
 
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).Select
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).Select
Selection.EntireRow.Hidden = True
Application.EnableEvents = True
End Sub
 
You went thru a pile of work to explain this to me... and I really appreciate
tihis and I think I did not explain myself very well.
My sheet conatins the folloiwng:

A B
c D
Name Address 12 Jan 2006
19 Jan 2006
Niceguy his address is here 2.00
5.00
Niceer guy HIS ADDRESS GOES HERE 4.00
not nice addess
2.50
Other guy address 12.00
1.25
The column beyond D are all dates during thsi year.
Here is what I would like
I place my cursor on any cell in col c and then hit a macro to show me only
the entries that contain data
In effect this would do that same as clcicking onf the filter arrow and
selection NON BLANK
Thanks in advance.
 
Well, same two options, but written to be placed into a regular code module.
Try them out and decide which one is best for you. Simply click a cell then
choose Tools | Macro | Macros and pick the one to test/use.

Sub HideEmptyRowCellsInSameColumn()
Dim thisColumn As String

Application.EnableEvents = False
thisColumn = Left(ActiveCell.Address, InStr(2, ActiveCell.Address, "$") -
1) _
& ":" & Left(ActiveCell.Address, InStr(2, ActiveCell.Address, "$") - 1)
Columns(thisColumn).Select
Selection.EntireRow.Hidden = False
Range(thisColumn).SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Hidden = True
Application.EnableEvents = True
End Sub
Sub HideEmptyColumnCellsInSameRow()
'hide empty cells on same row that double-click was in
Dim thisRow As String

Application.EnableEvents = False
thisRow = ActiveCell.Row & ":" & ActiveCell.Row
Rows(thisRow).Select
Selection.EntireColumn.Hidden = False
Range(thisRow).SpecialCells(xlCellTypeBlanks).Select
Selection.EntireColumn.Hidden = True
Application.EnableEvents = True
End Sub

You're probably want macros to display the hidden rows/columns later since
you don't have any way to click something in one of them once it becomes
hidden. You can put these in the same code module.

Sub UnhideColumns()
Dim WhereAmI As String
WhereAmI = ActiveCell.Address
Application.ScreenUpdating = False
Rows(ActiveCell.Row & ":" & ActiveCell.Row).Select
Selection.EntireColumn.Hidden = False
Range(WhereAmI).Select
Application.ScreenUpdating = True
End Sub

Sub UnhideRows()
Dim thisColumn As String
Dim WhereAmI As String
Application.ScreenUpdating = False
thisColumn = Left(ActiveCell.Address, InStr(2, ActiveCell.Address, "$") -
1) _
& ":" & Left(ActiveCell.Address, InStr(2, ActiveCell.Address, "$") - 1)
Columns(thisColumn).Select
Selection.EntireRow.Hidden = False
Range(WhereAmI).Select
Application.ScreenUpdating = True

End Sub
 
That did it...very many thanks
Ian M

JLatham said:
Well, same two options, but written to be placed into a regular code module.
Try them out and decide which one is best for you. Simply click a cell then
choose Tools | Macro | Macros and pick the one to test/use.

Sub HideEmptyRowCellsInSameColumn()
Dim thisColumn As String

Application.EnableEvents = False
thisColumn = Left(ActiveCell.Address, InStr(2, ActiveCell.Address, "$") -
1) _
& ":" & Left(ActiveCell.Address, InStr(2, ActiveCell.Address, "$") - 1)
Columns(thisColumn).Select
Selection.EntireRow.Hidden = False
Range(thisColumn).SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Hidden = True
Application.EnableEvents = True
End Sub
Sub HideEmptyColumnCellsInSameRow()
'hide empty cells on same row that double-click was in
Dim thisRow As String

Application.EnableEvents = False
thisRow = ActiveCell.Row & ":" & ActiveCell.Row
Rows(thisRow).Select
Selection.EntireColumn.Hidden = False
Range(thisRow).SpecialCells(xlCellTypeBlanks).Select
Selection.EntireColumn.Hidden = True
Application.EnableEvents = True
End Sub

You're probably want macros to display the hidden rows/columns later since
you don't have any way to click something in one of them once it becomes
hidden. You can put these in the same code module.

Sub UnhideColumns()
Dim WhereAmI As String
WhereAmI = ActiveCell.Address
Application.ScreenUpdating = False
Rows(ActiveCell.Row & ":" & ActiveCell.Row).Select
Selection.EntireColumn.Hidden = False
Range(WhereAmI).Select
Application.ScreenUpdating = True
End Sub

Sub UnhideRows()
Dim thisColumn As String
Dim WhereAmI As String
Application.ScreenUpdating = False
thisColumn = Left(ActiveCell.Address, InStr(2, ActiveCell.Address, "$") -
1) _
& ":" & Left(ActiveCell.Address, InStr(2, ActiveCell.Address, "$") - 1)
Columns(thisColumn).Select
Selection.EntireRow.Hidden = False
Range(WhereAmI).Select
Application.ScreenUpdating = True

End Sub
Click to expand...
 
BTW: I've been remiss during all of this. I really should thank member
PapaDos over at DSLReports.com for at least part of this. He and I
collaborated on helping someone with a similar request at that site and after
some rather dismal failures at coming up with a solution on my part, he came
up with the
Range(thisColumn).SpecialCells(xlCellTypeBlanks).Select
one-liner that really does the work efficiently and accurately.
 
Thanks JLatham !

I wiil try to participate here too, if I could get used to the way those
forums go...
;-]
 
Back
Top