Vba macro code for removing cells and appearing it continously

**Dharani Suresh** · 09-25-2013, 01:13 AM

Dear Sir /Madam,
The sheet 1 (input file) is the given file. i need output like in the Sheet2(required output sheet). For easy understanding i had given colors for your kind reference. from that lease remove the cell apsim version cell and the headings from the second set onwards.

in simple words I need output like in the second sheet. (hope I m not confusing)please do help me...

please oblige and do the needful... PLEASE FIND THE ATTACHMENT...

Thanks & Regards,
Dharani.
full files.xls

**patel45** · 09-25-2013, 02:45 AM

----------- deleted

**patel45** · 09-25-2013, 02:50 AM

Sub a()
Sheets(1).Copy after:=Sheets(1)
lr = Cells(Rows.Count, "B").End(xlUp).Row
Columns(1).Insert
Count = 1
For Each c In Range("B1:B" & lr).SpecialCells(xlConstants).Areas
    Set cc = c.Offset(, -1).Resize(, 21)
    cc.Select
    site = Right(cc.Cells(2, 2).Text, Len(cc.Cells(2, 2).Text) - 8)
    For j = 1 To 2: cc.Rows(1).Delete: Next
    cc.Resize(, 1).Value = site
    If Count > 1 Then
      cc.Rows(1).Delete
    Else
      cc.Cells(1, 1) = "Site"
    End If
    
    Count = Count + 1
Next c
End Sub

**Dharani Suresh** · 09-25-2013, 04:48 AM

Originally Posted by patel45

Sub a()
Sheets(1).Copy after:=Sheets(1)
lr = Cells(Rows.Count, "B").End(xlUp).Row
Columns(1).Insert, 1) = "Site"
    Count = Count + 1
Next c
End Sub

Dear Patel,
My heartfelt Thanks for you for the honest and quick effort you made for my help... but Sir the code is not seems to be correct which means it is not giving the required output which I want... it is giving back the sheet 1 data only... I need output like which is in sheet 2.

Please reply Sir....

Awaiting for the positive response....

Thanks & Regards,
Dharani.

**venkat1926** · 09-25-2013, 03:33 AM

see attched file "Dharani ,,,,,". first and second sheets are yours. I have added a sheet called "result"
my version is excel 2007(compatablity mode).I hope the macro will work in your excel 2003
the mcro is in the module but also repeated here

if there is problem in donwloading the file revert to the newsgsroup

Sub test()
    Dim r As Range, cfind1 As Range, cfind2 As Range, add As String, title As String
    Dim llastcell As Range, hdng As Range
    Worksheets("result").Cells.Clear
    Worksheets("input file").Cells.Copy Worksheets("result").Range("A1")
    Worksheets("result").Activate
    ActiveSheet.DrawingObjects.Delete
    Set r = ActiveSheet.UsedRange
    r.AutoFilter Field:=1, Criteria1:="apsim*"
    r.SpecialCells(xlCellTypeVisible).EntireRow.Delete
    Range("a1").EntireColumn.Insert

    Set llastcell = Cells(Rows.Count, "B").End(xlUp)

    Set cfind1 = Cells.Find(what:="title", lookat:=xlPart, after:=llastcell)
    add = cfind1.Address
    title = Right(cfind1, Len(cfind1) - 8)
    Range(cfind1.Offset(2, -1), cfind1.End(xlDown).Offset(0, -1)).FormulaArray = title
    Do
        Set cfind1 = Cells.FindNext(cfind1)
        If cfind1 Is Nothing Then Exit Do
        If cfind1.Address = add Then Exit Do
        title = Right(cfind1, Len(cfind1) - 8)
        Range(cfind1.Offset(2, -1), cfind1.End(xlDown).Offset(0, -1)).FormulaArray = title
    Loop
    Set r = ActiveSheet.UsedRange
    r.AutoFilter Field:=2, Criteria1:="Title*"
    r.SpecialCells(xlCellTypeVisible).EntireRow.Delete
    r.AutoFilter Field:=2, Criteria1:="Season"
    r.Offset(1, 0).Resize(r.Rows.Count - 1).EntireRow.Delete
    ActiveSheet.AutoFilterMode = False
    Range("a1") = "Title"
    ActiveSheet.Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Range("A1").Select
    MsgBox "macro over see sheet result"
End Sub

**Dharani Suresh** · 09-25-2013, 04:44 AM

Originally Posted by venkat1926

see attched file "Dharani ,,,,,". first and second sheets are yours. I have added a sheet called "result"
if there is problem in donwloading the file revert to the newsgsroup

Sub test()
    Dim r As Range, cfind1 As Range, cfind2 As Range, add As String, title As String
    Dim llastcell As Range, hdng As Range
    Worksheets("result").Cells.Clear
        MsgBox "macro over see sheet result"
End Sub

Dear Venkat,
Thank you for the amazing code... its working... My heartfelt thanks to you for you honest and quick effort you made... a small clarification is required.. this code is working only in your attached excel sheet. if I copy and paste the code in full files excel sheet, which I attached above with my question, it is not working... please reply...

Awaiting for a positive reply..

Thanks & Regards,
Dharani.

**Dharani Suresh** · 09-25-2013, 11:26 PM

Originally Posted by Dharani Suresh

Dear Venkat,
Thank you for the amazing code... its working... My heartfelt thanks to you for you honest and quick effort you made... a small clarification is required.. this code is working only in your attached excel sheet. if I copy and paste the code in full files excel sheet, which I attached above with my question, it is not working... please reply...

Awaiting for a positive reply..

Thanks & Regards,
Dharani.

**patel45** · 09-25-2013, 05:55 AM

I'm sorry, but my code works on your attached file, it makes a copy of sheet1 then changes it, explain better please

**Dharani Suresh** · 09-25-2013, 07:37 AM

Originally Posted by patel45

I'm sorry, but my code works on your attached file, it makes a copy of sheet1 then changes it, explain better please

Dear Patel,
I m sorry actually it is giving the same input only in the next sheet for me and it is showing error as some 400. sir I m attaching you the excel file with your code and output for your kind reference and perusal. patel sir.xls

Awaiting for a positive reply..
Thanks & Regards,
Dharani.

**patel45** · 09-25-2013, 11:44 AM

Originally Posted by Dharani Suresh

Dear Patel,
I m sorry actually it is giving the same input only in the next sheet for me and it is showing error as some 400. sir I m attaching you the excel file with your code and output for your kind reference and perusal.

Please, remove all images and comments and try my code again

**Dharani Suresh** · 09-25-2013, 11:23 PM

Originally Posted by patel45

Please, remove all images and comments and try my code again

Dear Patel,
I am really sorry to tell this.. for me its not working, maybe I m not clear to you.. That's ok Sir, leave it.I must not take your time much... maybe not this time, for my next question, I hope your contribution will be more useful and helpful.

My heartfelt thanks to you..

Regards,
Dharani.

**AB33** · 09-25-2013, 07:58 AM

The attach code works on your attached sample.

**Dharani Suresh** · 09-25-2013, 11:18 PM

Originally Posted by AB33

The attach code works on your attached sample.

Dear AB33,
Thank you for the honest and quick effort you made.... but see at the column DATES (c column) there are some mistakes. dates are wrongly appearing. if you rectify and give this, it would be highly helpful for me.

Awaiting for a positive response...

Thanks & Regards,
Dharani.

**AB33** · 09-26-2013, 05:15 AM

Dates are formatting issue and no error with the code.
You can simply highlight the entire column C and format it as data. I have tried to include a line on the output code, but does not make any difference. If I include the formatting on the source sheet, it seems to work. See attached amended code.

**Dharani Suresh** · 09-26-2013, 11:50 PM

Originally Posted by AB33

Dates are formatting issue and no error with the code.
You can simply highlight the entire column C and format it as data. I have tried to include a line on the output code, but does not make any difference. If I include the formatting on the source sheet, it seems to work. See attached amended code.

Dear AB33,
Thanks for the apt answer... thank you so much for spending your valuable time... the code is working.. its GREAT...
But a small request .. can you attach me the code separately???? because I could not find the code in module.

Thanks & Regards,
Dharani.

**AB33** · 09-27-2013, 09:26 AM

Dharani,
If you had run the code on the actual sheet, you should have run it from the module as there is not button attached to it. On the attached post #14, there are only two modules. The code is in module 1. You have also recorded macro on module 2.

Vba macro code for removing cells and appearing it continously

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Vba macro code for removing cells and appearing it continously

Re: Vba macro code for removing cells and appearing it continously

Re: Vba macro code for removing cells and appearing it continously

Re: Vba macro code for removing cells and appearing it continously

Re: Vba macro code for removing cells and appearing it continously

Re: Vba macro code for removing cells and appearing it continously

Re: Vba macro code for removing cells and appearing it continously

Re: Vba macro code for removing cells and appearing it continously

Re: Vba macro code for removing cells and appearing it continously

Re: Vba macro code for removing cells and appearing it continously

Re: Vba macro code for removing cells and appearing it continously

Re: Vba macro code for removing cells and appearing it continously

Re: Vba macro code for removing cells and appearing it continously

Re: Vba macro code for removing cells and appearing it continously

Re: Vba macro code for removing cells and appearing it continously

Re: Vba macro code for removing cells and appearing it continously

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Vba macro code for generating years along the side of days and also by removing the months

Macro to Copy down and insert line continously

Removing Duplicated Cells Macro

Continously updated data from older files?

Removing <end> from appearing in outlook contacts

Bookmarks

Bookmarks

Posting Permissions