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

Hide rows without data in a column based on active cell
 

Hi Johan,

Am Wed, 9 Mar 2016 13:48:28 -0800 (PST) schrieb :

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.

try dollowing code in the module of the expected worksheet:

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(2, 1), Cells(2, LCol))) Is _
Nothing Or Target.Count 1 Then Exit Sub

If Target.Column = 1 Then
ActiveSheet.ShowAllData
Else
ActiveSheet.UsedRange.AutoFilter field:=Target.Column, Criteria1:="<" & ""
End If
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

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

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

Copy text in colum each time it is a new text
 

Hi,

Am Thu, 10 Mar 2016 15:34:37 GMT schrieb :

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

try:

Sub Test()
Dim LRow As Long, i As Long
Dim varData As Variant

With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
varData = .Range("A1:C" & LRow + 1)
End With

For i = 2 To UBound(varData)
If varData(i, 3) < varData(i - 1, 3) Then
Sheets("test").Cells(Rows.Count, 1).End(xlUp)(2) _
.Resize(1, 3) = Application.Index(varData, i - 1)
End If
Next
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

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

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

Copy text in colum each time it is a new text
 

It is working fine.
Thank you.

Copy text in colum each time it is a new text
 

It is working fine.
Thank you very much.

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 :)