Delete multi columns

[loay]

Dear all
I have a large amount of data I need to delete all the columns contains EAS1, EAS2, and EAS3…… so on
How I can do that using VBA
thanks

[Otto Moehrbach]

Not sure of the condition for deletion. Is it the column contains just one
entry whose first 3 characters are "EAS"? Or must the column contain more
than one? How many? Are these criteria entries always in the same row or
anywhere in the column? HTH Otto
"loay" <[email protected]> wrote in message
news:[email protected]...
>
> Dear all
> I have a large amount of data I need to delete all the columns contains
> EAS1, EAS2, and EAS3.. so on
> How I can do that using VBA
> thanks
>
>
> --
> loay
> ------------------------------------------------------------------------
> loay's Profile:
> http://www.excelforum.com/member.php...o&userid=33721
> View this thread: http://www.excelforum.com/showthread...hreadid=535043
>

[Tom Ogilvy]

Sub DeleteColumns()
Dim rng As Range
Set rng = Cells.Find(What:="EAS", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
Do
rng.EntireColumn.Delete
Set rng = Cells.FindNext(ActiveCell)
Loop While Not rng Is Nothing
End If
End Sub

Test it on a copy of your workbook

--
Regards,
Tom Ogilvy


"loay" wrote:

>
> Dear all
> I have a large amount of data I need to delete all the columns contains
> EAS1, EAS2, and EAS3…… so on
> How I can do that using VBA
> thanks
>
>
> --
> loay
> ------------------------------------------------------------------------
> loay's Profile: http://www.excelforum.com/member.php...o&userid=33721
> View this thread: http://www.excelforum.com/showthread...hreadid=535043
>
>

[loay]

The first raw of the sheet will contain the EAS1, EAS2 and EAS3....and so on as a header 10% of the columns have this header and the hole column must deleted

Thanks

[Tom Ogilvy]

Sub DeleteColumns()
Dim rng As Range
Set rng = Rows(1).Find(What:="EAS", _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
Do
rng.EntireColumn.Delete
Set rng = Rows(1).FindNext(Range("A1"))
Loop While Not rng Is Nothing
End If
End Sub

--
Regards,
Tom Ogilvy


"loay" <[email protected]> wrote in message
news:[email protected]...
>
> The first raw of the sheet will contain the EAS1, EAS2 and EAS3....and
> so on as a header 10% of the columns have this header and the hole
> column must deleted
>
> Thanks
>
>
> --
> loay
> ------------------------------------------------------------------------
> loay's Profile:
http://www.excelforum.com/member.php...o&userid=33721
> View this thread: http://www.excelforum.com/showthread...hreadid=535043
>

[loay]

Thanks alot it works very well and solve my problem

[loay]

Dear All

How i can copy the same columns (Contains EAS1,EAS2 and EAS3...) to another sheet can i use the same VBA macro with some modifys

Regards
Loay

[Tom Ogilvy]

Sub DeleteColumns()
Dim rng As Range
Dim rng1 as Range
Set rng = Rows(1).Find(What:="EAS", _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
Do
if rng1 is nothing then
set rng1 = rng
else
set rng1 = union(rng1,rng)
end if
Set rng = Rows(1).FindNext(Range("A1"))
Loop While Not rng Is Nothing
End If
if not rng1 is nothing then
rng1.EntireColumn.Copy Worksheets("Sheet2").Range("A1")
End if
End Sub

--
Regards,
Tom Ogilvy


"loay" <[email protected]> wrote in message
news:[email protected]...
>
> Dear All
>
> How i can copy the same columns (Contains EAS1,EAS2 and EAS3...) to
> another sheet can i use the same VBA macro with some modifys
>
> Regards
> Loay
>
>
> --
> loay
> ------------------------------------------------------------------------
> loay's Profile:
http://www.excelforum.com/member.php...o&userid=33721
> View this thread: http://www.excelforum.com/showthread...hreadid=535043
>

[Tom Ogilvy]

Disregard that - some more changes needed to be made:

Sub CopyColumns()
Dim rng As Range
Dim rng1 As Range
Set rng = Rows(1).Find(What:="EAS", _
After:=Range("IV1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
sAddr = rng.Address
Do
If rng1 Is Nothing Then
Set rng1 = rng
Else
Set rng1 = Union(rng1, rng)
End If
Set rng = Rows(1).FindNext(rng)
Loop While rng.Address <> sAddr
End If
If Not rng1 Is Nothing Then
rng1.EntireColumn.Copy _
Worksheets("Sheet2").Range("A1")
End If
End Sub


--
Regards,
Tom Ogilvy

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> Sub DeleteColumns()
> Dim rng As Range
> Dim rng1 as Range
> Set rng = Rows(1).Find(What:="EAS", _
> After:=Range("A1"), _
> LookIn:=xlFormulas, _
> LookAt:=xlPart, _
> SearchOrder:=xlByColumns, _
> SearchDirection:=xlNext, _
> MatchCase:=False)
> If Not rng Is Nothing Then
> Do
> if rng1 is nothing then
> set rng1 = rng
> else
> set rng1 = union(rng1,rng)
> end if
> Set rng = Rows(1).FindNext(Range("A1"))
> Loop While Not rng Is Nothing
> End If
> if not rng1 is nothing then
> rng1.EntireColumn.Copy Worksheets("Sheet2").Range("A1")
> End if
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
>
> "loay" <[email protected]> wrote in
message
> news:[email protected]...
> >
> > Dear All
> >
> > How i can copy the same columns (Contains EAS1,EAS2 and EAS3...) to
> > another sheet can i use the same VBA macro with some modifys
> >
> > Regards
> > Loay
> >
> >
> > --
> > loay
> > ------------------------------------------------------------------------
> > loay's Profile:
> http://www.excelforum.com/member.php...o&userid=33721
> > View this thread:
http://www.excelforum.com/showthread...hreadid=535043
> >
>
>