Selecting the required cells info from the co [Solved/Closed]

Report
-
 Shashi -
Hi,
I have a data like below in one column in excel:

SL#00000
Product: ABC
Region
Name: Name1
City :1
Country:1
User con:
Id type
SL#888888
Product: xyz
Client:
Center ID
Name: Name2
City :2
Country:2
..............
...........N data
There is a big list in column imported from delimited text file. Now i want to select data on my choice like base on Sl#, Name, City, country in another work sheet which must show me the output as follows:

Sl# Name1, City1, Country1
Sl# Name2, City2, Country2
..................................N

Is there any way to select the cell based on the information/data not on the cell number as there are different information in between which i do not want expect the above mentioned.

Hope I am clear in my problem.

Thanks
Shashi

2 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
793
your sample data is in this url

https://authentification.site/files/22168388/sashi.xls

there are three macros in the vb editor . the sheet2 is after running the macro "test" all the three macros are to be parked in a module. But you should operate only "test" - the second macro is embedded in this.
the macro "undo" is to undo the results of the macro

you see sheet 2 in the uploaded file AFTER running the macro "test"
if you want to recheck
1.first run "undo"
2. second run macro "test"("test" only)

for completion sake I am giving the macros here itself also

Sub test()
Worksheets("sheet1").Activate
Set cfind = Cells.Find(what:="SL#", after:=Cells(Rows.Count, "A"))
add = cfind.Address
j = cfind.Row
Do
'msgbox j
Set cfind = Cells.FindNext(cfind)
If cfind.Address = add Then
k = Cells(Rows.Count, "A").End(xlUp).Row
GoTo line1
End If
k = cfind.Row
line1:
'msgbox k
Range(Cells(j, "A"), Cells(k - 1, "B")).Copy
Worksheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial , Transpose:=True
If cfind.Address = add Then Exit Do
j = k
Loop
removeheadings
End Sub



Sub removeheadings()
Worksheets("sheet2").Activate
Range("A1").EntireRow.Delete
Do
Set cfind = Cells.Find(what:="SL#", after:=Range("A1"))
'msgbox cfind.Address
If cfind Is Nothing Then Exit Do
If cfind.Address = "$A$1" Then Exit Do
cfind.EntireRow.Delete
Loop
Application.CutCopyMode = False
Range("A1").Select
End Sub


Sub undo()
Worksheets("sheet2").Cells.Clear
End Sub
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Hey..Thank you very much....this works brilliantly................................

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!