Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Select NON blank cells
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Select NON blank cells
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 he 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 "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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Select NON blank cells
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. "JLatham" wrote: 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 he 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 "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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Select NON blank cells
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).S elect 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).Sele ct 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 "pcor" wrote: 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. "JLatham" wrote: 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 he 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 "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 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Select NON blank cells
That did it...very many thanks
Ian M "JLatham" wrote: 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).S elect 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).Sele ct 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 "pcor" wrote: 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. "JLatham" wrote: 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 he 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 "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 |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Select NON blank cells
You're welcome. Enjoy.
"pcor" wrote: That did it...very many thanks Ian M "JLatham" wrote: 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).S elect 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).Sele ct 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 "pcor" wrote: 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. "JLatham" wrote: 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 he 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 "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 |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Select NON blank cells
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).S elect one-liner that really does the work efficiently and accurately. "pcor" wrote: That did it...very many thanks Ian M "JLatham" wrote: 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).S elect 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).Sele ct 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 "pcor" wrote: 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. "JLatham" wrote: 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 he 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 "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 |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Select NON blank cells
Thanks JLatham !
I wiil try to participate here too, if I could get used to the way those forums go... ;-] -- Festina Lente "JLatham" wrote: 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).S elect one-liner that really does the work efficiently and accurately. "pcor" wrote: That did it...very many thanks Ian M "JLatham" wrote: 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).S elect 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).Sele ct 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 "pcor" wrote: 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. "JLatham" wrote: 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 he 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 "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically add "0" to blank cells without a formula in the cel. | Excel Worksheet Functions | |||
Blank Rows from Merged Cells in Drop Down Menu | Excel Discussion (Misc queries) | |||
Generating truly blank cells | Excel Worksheet Functions | |||
enter numbers in blank cells | Excel Worksheet Functions | |||
blank cells | Excel Discussion (Misc queries) |