Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide rows without data in a column based on active cell
Hello,
I like to have a module which takes below action. The mean heading is row 2. When selecting one of the records in row 2 (for example cell D2) I want that a module automatic (OnActivate) hide all the rows from row 3 till last where in this same column as the active cell there's no data (empty cell). For example. I select cell D2 and in cell D3, D5, D8, D10 is no data. Then the module should hide those rows. When selecting the field A1 all hiden rows should be unhide. When I select for example then cell F2, and in this column the cells F8, F10 are empty, then those empty rows should be hiden. Somebody can help me out ? regards, Johan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide rows without data in a column based on active cell
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide rows without data in a column based on active cell
Hi Johan,
Am Wed, 9 Mar 2016 23:04:00 +0100 schrieb Claus Busch: try dollowing code in the module of the expected worksheet: sorry, I didn't read carefully. Try instead: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim LCol As Long LCol = Cells(2, Columns.Count).End(xlToLeft).Column If Intersect(Target, Range(Cells(1, 1), Cells(2, LCol))) Is _ Nothing Or Target.Count 1 Then Exit Sub If Target.Row = 1 Then ActiveSheet.AutoFilterMode = False Else ActiveSheet.UsedRange.AutoFilter field:=Target.Column, _ Criteria1:="<" & "" End If End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy text in colum each time it is a new text
Can someone help with a macro?
In column C I have the same text in a various number of rows, but always after each other. Each time the text in column C changes I want the macro to do this: To a new sheet, caled "test" copy the number from "C"one time and also copy the belonging contents of column A+B I want the numbers under each other in sheet test Example: A B C 20160311 3635127 X8YELLOW 20160311 3635127 X8YELLOW 20160311 3635127 X8YELLOW 20160310 6355014 G9BLACK 20160310 6355014 G9BLACK 20160310 6355014 G9BLACK The sheet test should look like this: 20160311 3635127 X8YELLOW 20160310 6355014 G9BLACK I also want to define the range to be looked through. Best regards, Kaj Pedersen |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy text in colum each time it is a new text
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy text in colum each time it is a new text
The macro does exactly what I want. Thank you very much.
But can I define the range to be looked through? I tried to insert this in the macro, but it did not work. Range("A6:C48").Select Best regards, Kaj Pedersen |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy text in colum each time it is a new text
Hi,
Am Thu, 10 Mar 2016 17:54:51 GMT schrieb : But can I define the range to be looked through? I tried to insert this in the macro, but it did not work. Range("A6:C48").Select try it this way: Sub UniqueValues() ActiveSheet.Range("A6:C48").Copy Sheets("test").Range("A1") Sheets("test").UsedRange.RemoveDuplicates Columns:=3, Header:=xlNo End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy text in colum each time it is a new text
It is working fine.
Thank you. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy text in colum each time it is a new text
It is working fine.
Thank you very much. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide rows without data in a column based on active cell
Op woensdag 9 maart 2016 23:11:30 UTC+1 schreef Claus Busch:
Hi Johan, Am Wed, 9 Mar 2016 23:04:00 +0100 schrieb Claus Busch: try dollowing code in the module of the expected worksheet: sorry, I didn't read carefully. Try instead: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim LCol As Long LCol = Cells(2, Columns.Count).End(xlToLeft).Column If Intersect(Target, Range(Cells(1, 1), Cells(2, LCol))) Is _ Nothing Or Target.Count 1 Then Exit Sub If Target.Row = 1 Then ActiveSheet.AutoFilterMode = False Else ActiveSheet.UsedRange.AutoFilter field:=Target.Column, _ Criteria1:="<" & "" End If End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional THANKS A LOT. WORKS VERY GOOD !!!!!!!!!!!!!! Regards, Johan :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide rows based on cell data | Excel Programming | |||
Hide/Show Rows based on Cell Value with Data Validation | Excel Programming | |||
Macro to Hide rows based on value of column F | Excel Discussion (Misc queries) | |||
How to hide a column once any cell of that col. is not active any | Excel Programming | |||
chk box to filter or hide rows based on value in a column | Excel Programming |