Thanks a tonn Alpeshah... Macro provided by you solves the Segregation part...
Can any other group member help me out for workbook creation? *Conditions/Criteria* > 1. Delete columns C,I, U,X,Y,Z - Solved by Alpeshah > > 2. Delete the rows which has "180" in column A - Solved by Alpeshah > > 3. Delete rows which has "WW","Z0","Z1","Z2" in Column D ( after the step1, > column D will become column C) - Solved by Alpeshah > 4. Create different files for each number in Column A and save it in a > folder on Desktop/My Document (File Name - Today's Date in mmddyy format > "sub" and number in Column A. For e.g., 073110 sub 4.xlsx) - *Macro creation pending** - Please HELP!!!!* Thanks VInod N On Mon, Aug 9, 2010 at 6:32 AM, pesh <alpes...@gmail.com> wrote: > Hi Vinod, > > This is not the most efficient way, however I did not have enough time > to make it ideal. > I have re-used code found on the web in certain cases, source include: > http://www.rondebruin.nl/delete.htm for the delete row. > > Sub code 1 deletes 180 value > Sub code 2 deletes "Z0,1,2,WW" > Sub code 3 deletes Columns mentioned previously > > Hope this helps. > Alpesh > > > Sub Loop_Example_RowA() > Dim Firstrow As Long > Dim Lastrow As Long > Dim Lrow As Long > Dim CalcMode As Long > Dim ViewMode As Long > > With Application > CalcMode = .Calculation > .Calculation = xlCalculationManual > .ScreenUpdating = False > End With > > 'We use the ActiveSheet but you can replace this with > 'Sheets("MySheet")if you want > With ActiveSheet > > 'We select the sheet so we can change the window view > .Select > > 'If you are in Page Break Preview Or Page Layout view go > 'back to normal view, we do this for speed > ViewMode = ActiveWindow.View > ActiveWindow.View = xlNormalView > > 'Turn off Page Breaks, we do this for speed > .DisplayPageBreaks = False > > 'Set the first and last row to loop through > Firstrow = .UsedRange.Cells(1).Row > Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row > > 'We loop from Lastrow to Firstrow (bottom to top) > For Lrow = Lastrow To Firstrow Step -1 > > 'We check the values in the A column in this example > With .Cells(Lrow, "A") > > If Not IsError(.Value) Then > > If .Value = "180" Then .EntireRow.Delete > 'This will delete each row with the Value "ron" > 'in Column A, case sensitive. > > End If > > End With > > Next Lrow > > End With > > > For Lrow = Lastrow To Firstrow Step -1 > > 'We check the values in the A column in this example > With .Cells(Lrow, "D") > > If Not IsError(.Value) Then > > If (.Value = "WW" Or .Value = "Z0" Or .Value = > "Z1" Or .Value = "Z2") Then .EntireRow.Delete > > > > End If > > End With > > Next Lrow > > > ActiveWindow.View = ViewMode > With Application > .ScreenUpdating = True > .Calculation = CalcMode > End With > > End Sub > > Sub Loop_Example_RowD() > Dim Firstrow As Long > Dim Lastrow As Long > Dim Lrow As Long > Dim CalcMode As Long > Dim ViewMode As Long > > With Application > CalcMode = .Calculation > .Calculation = xlCalculationManual > .ScreenUpdating = False > End With > > 'We use the ActiveSheet but you can replace this with > 'Sheets("MySheet")if you want > With ActiveSheet > > 'We select the sheet so we can change the window view > .Select > > 'If you are in Page Break Preview Or Page Layout view go > 'back to normal view, we do this for speed > ViewMode = ActiveWindow.View > ActiveWindow.View = xlNormalView > > 'Turn off Page Breaks, we do this for speed > .DisplayPageBreaks = False > > 'Set the first and last row to loop through > Firstrow = .UsedRange.Cells(1).Row > Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row > > 'We loop from Lastrow to Firstrow (bottom to top) > For Lrow = Lastrow To Firstrow Step -1 > > 'We check the values in the A column in this example > With .Cells(Lrow, "D") > > If Not IsError(.Value) Then > > If (.Value = "WW" Or .Value = "Z0" Or .Value = > "Z1" Or .Value = "Z2") Then .EntireRow.Delete > ElseIf (.Value = "Z0") Then .EntireRow.Delete > ElseIf (.Value = "Z1") Then .EntireRow.Delete > ElseIf (.Value = "Z2") Then .EntireRow.Delete > > > 'This will delete each row with the Value "ron" > 'in Column A, case sensitive. > > End If > > End With > > Next Lrow > > End With > > ActiveWindow.View = ViewMode > With Application > .ScreenUpdating = True > .Calculation = CalcMode > End With > > End Sub > > Sub Delete_Column_Excel_VBA() > > > Columns("C:C").Select > Columns("I:I").Select > Columns("U:U").Select > Columns("X:X").Select > Columns("Y:Y").Select > Columns("Z:Z").Select > Selection.Delete Shift:=xlToLeft > > End Sub > > > > On Aug 8, 12:58 am, Vinod N <nvino...@gmail.com> wrote: > > Hi Friends, > > > > Please help by creating a macro for on the attached file... > > > > *Conditions/Criteria* > > 1. Delete columns C,I, U,X,Y,Z > > > > 2. Delete the rows which has "180" in column A > > > > 3. Delete rows which has "WW","Z0","Z1","Z2" in Column D ( after the > step1, > > column D will become column C) > > > > 4. Create different files for each number in Column A and save it in a > > folder on Desktop/My Document (File Name - Today's Date in mmddyy format > > "sub" and number in Column A. For e.g., 073110 sub 4.xlsx) > > > > Thanks > > VInod N---------- Forwarded message ---------- > > From: Vinod N <nvino...@gmail.com> > > Date: Thu, Aug 5, 2010 at 1:20 PM > > Subject: Fwd: $$Excel-Macros$$ Macro for Segrating and file creation > > To: excel-macros <excel-macros@googlegroups.com> > > > > Hi Gurus, > > > > Please help..!!!! > > > > ---------- Forwarded message ---------- > > From: Vinod N <nvino...@gmail.com> > > Date: Sat, Jul 31, 2010 at 9:13 AM > > Subject: $$Excel-Macros$$ Macro for Segrating and file creation > > To: excel-macros <excel-macros@googlegroups.com> > > > > Hi, > > > > Please help me by creating a macro. > > > > 1. Sample data and criterias are given in Details.xlsx > > 2. 073110 sub 7.xlsx is one of the resultant file > > > > -- > > Thanks and Regards > > > > Vinod Nhttp://cavinod.blogspot.com/ > > > > -- > > > --------------------------------------------------------------------------- > ------- > > Some important links for excel users: > > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 > > 3. Excel tutorials athttp://www.excel-macros.blogspot.com > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com > > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com > > > > To post to this group, send email to excel-macros@googlegroups.com > > > > <><><><><><><><><><><><><><><><><><><><><><> > > HELP US GROW !! > > > > We reach over 7000 subscribers worldwide and receive many nice notes > about > > the learning and support from the group.Let friends and co-workers know > they > > can subscribe to group athttp:// > groups.google.com/group/excel-macros/subscribe > > > > -- > > Thanks and Regards > > > > Vinod Nhttp://cavinod.blogspot.com/ > > > > -- > > Thanks and Regards > > > > Vinod Nhttp://cavinod.blogspot.com/ > > > > > > > > Details.xlsx > > 189KViewDownload > > > > 073110 sub 7.xlsx > > 13KViewDownload > > -- > > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > HELP US GROW !! > > We reach over 7000 subscribers worldwide and receive many nice notes about > the learning and support from the group.Let friends and co-workers know they > can subscribe to group at > http://groups.google.com/group/excel-macros/subscribe > -- Thanks and Regards Vinod N http://cavinod.blogspot.com/ -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
