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
