Urgent Help needed for Macro - choosing rows needed

[Amarantine]

Hi All,

I have a group of huge data as follows. I want to create a macro to just choose the first immediate date of each individual (in another word hide the second rows onwards for each Name). How can i do it? Thanks in advance for any help.

Name	Travelling date
A	2-3-2014
A	4-4-2014
A	5-7-2014
B	3-3-2014
B	8-9-2014

<TBODY>
</TBODY>

 

[Amarantine]

Anyone can save me here?....

 

[dispelthemyth]

What you want can be done without VBA, a simple formulas as below will do the trick, you just need to apply a filter

Excel Workbook
	A	B	C
1	Name	Travelling date	Helper Column
2	A	02/03/2014	Don't Hide
3	A	04/04/2014	Hide
4	A	05/07/2014	Hide
5	B	03/03/2014	Don't Hide
6	B	08/09/2014	Hide
Sheet3

 

[Amarantine]

Hi dispelthemyth,

Thank you so much for your suggestion. I have think about this way also but that can't really help as i'm having more than 1000 names so it's very hard for me as the date will change over time and i need to generate the report everyday to track the immediate date for each of the employee...

 

[dmt32]

Hi All,

I have a group of huge data as follows. I want to create a macro to just choose the first immediate date of each individual (in another word hide the second rows onwards for each Name). How can i do it? Thanks in advance for any help.

Name	Travelling date
A	2-3-2014
A	4-4-2014
A	5-7-2014
B	3-3-2014
B	8-9-2014

<TBODY>
</TBODY>

Hi,
By first immediate date of each individual I assume that you mean the closest date to the current date for each employee? If this is so table above would show:

A 02/03/2014
B 03/03/2014

if I am on the right track the following may (or may not) do what you want.

I have made following assumptions:

- the first row is a header row
- names are in Column A
- Dates are in Column B
- your data sheet is named Sheet1 -
Change where shown in RED if required

For code to work I have allocated two helper cells (J1 & K1) if these cells do something else, you will need to modify the code as required

Place code in a standard module:

Sub ShowNearestDates()
    Dim c As Range, HideRange As Range, DataRange As Range
    Dim DateNearest As Range
    Dim StaffName As Range
    
   'your data master sheet rename as required
    With Worksheets("Sheet1")
        RowCount = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set DataRange = .Range("A1:A" & RowCount)
        
        'helper cells change as required
        Set DateNearest = .Range("K1")
        Set StaffName = .Range("J1")
    
    End With
    
    DateNearest.FormulaArray = "=MIN(IF(A2:A" & RowCount & "=" & StaffName.Address & ",IF(B2:B" & RowCount & ">=TODAY(),B2:B" & RowCount & ")))"
    
   DataRange.EntireRow.Hidden = False
    
    For Each c In DataRange.Cells
        StaffName.Value = c.Value
        If c.Offset(0, 1).Value <> DateNearest.Value Then
            If HideRange Is Nothing Then
                Set HideRange = c
            Else
                Set HideRange = Union(HideRange, c)
            End If
        End If
    Next c
    If Not HideRange Is Nothing Then HideRange.EntireRow.Hidden = True
End Sub

I have not been able to fully test and this is just a best guess at what you are looking for - hopefully it will work for you but you should be able to develop as required.

Hope helpful

Dave.

 

[blaow]

His formula didn't use the date column. I think you should take another look at dispelthemyth's suggestion, it does exactly what you want. After dragging the formula down, apply filters and select Don't Hide. Voila - you have a list of only the first occurrence of each name.

 

[XLfriend777]

Hi Amarantine - You can try the code below after adjusting the range. Try it on sample data on a small range first to see if it does what you want it to. Hope this helps.

Sub Amarantine_HideRows()
For Each c In Range("A2:A34")
    If c.value = c.Offset(-1, 0) Then
        c.EntireRow.Select
        Selection.EntireRow.Hidden = True
    End If
Next
End Sub

 

[Amarantine]

Thank you everyone for your reply and really appreciate. I will do a test on the above code! Thanks again!!