Selecting the required cells info from the co
Solved/Closed
Related:
- Selecting the required cells info from the co
- Clear only the formatting from the selected cell (leaving the content) - Guide
- The volume does not contain a recognized file system. please make sure that all required file system drivers are loaded and that the volume is not corrupted. - Hardware Forum
- Apply conditional formatting to the selected cells ✓ - Excel Forum
- Compare 2 text cells and show difference in the third cell ✓ - Excel Forum
- Netflix update payment info - Guide
2 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Apr 28, 2010 at 07:20 AM
Apr 28, 2010 at 07:20 AM
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
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
