Filter
You are welcome! Thanks for the feedback!
Stefi
€˛Mandeep Dhami€¯ ezt Ć*rta:
Thanks a lot Stefi.
Both the macros are working wonderfully and I am getting the results as I
expected.
Thanks once again....
Cheers,
Mandeep
"Stefi" wrote:
Another idea:
Sub SingleCust()
custid = InputBox("Enter a Customer ID!")
On Error GoTo NoCust
Columns("A").Find(What:=custid, _
After:=Range("A1"), _
LookAt:=xlWhole, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Activate
firstrow = ActiveCell.Row
' If firstrow 0 Then
lastrow = Columns("A").Find(What:="*", _
After:=ActiveCell, _
LookAt:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Row - 1
If lastrow < firstrow Then
lastrow = Columns("B").Find(What:="*", _
After:=Range("B1"), _
LookAt:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
End If
If firstrow 2 Then
Range("A2:A" & firstrow - 1).EntireRow.Hidden = True
End If
Range("A" & lastrow + 1 & ":A65536").EntireRow.Hidden = True
Exit Sub
NoCust:
MsgBox custid & " does not exist!"
End Sub
Regards,
Stefi
€˛Stefi€¯ ezt Ć*rta:
Hi Mandeep,
What about these subs:
Sub SingleCust()
firstrow = ActiveCell.Row
lastrow = Columns("A").Find(What:="*", _
After:=ActiveCell, _
LookAt:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Row - 1
If lastrow < firstrow Then
lastrow = Columns("B").Find(What:="*", _
After:=Range("B1"), _
LookAt:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
End If
If firstrow 2 Then
Range("A2:A" & firstrow - 1).EntireRow.Hidden = True
End If
Range("A" & lastrow + 1 & ":A65536").EntireRow.Hidden = True
End Sub
Sub AllCust()
Cells.EntireRow.Hidden = False
End Sub
Usage:
Select cell (in Column A) with Cust.ID you want to see and run Singlecust
(you may assign a hot key to it).
Run AllCust to make all rows visible again!
Regards,
Stefi
€˛Mandeep Dhami€¯ ezt Ć*rta:
Hi,
I have huge data.
Column A has Customer ID. Data here is not repeated untill next change in
Customer ID.
Column B have Invoice Nos. There are many rows for 1 customer ID.
Column C have Invoice Date. There are many rows for 1 customer ID.
Column D....E....F....& so on.
I want data for any one selected Customer ID along with all the invoice
numbers, invoice dates & so on.
Using filter in Coulmn A we don't get the data as I require as the
immediately next few rows for the Customer ID in column A will be blank.
One solution is to copy the customer ID from column A & paste untill the new
one is spotted, but this is very time consuming exercise. I have data running
into tens of thousand of lines.
Please suggest how can I get the required result.
Cheers,
Mandeep
|