I'm trying to programmatically delete columns based on a criteria.

If A1=1 and B1=2, do nothing.

If A1=1, B1<>2, C1<>2 and D1=2, delete columns B:C.

I could test with the following:

If Application.Match(1, Range("Sheet1!$1:$1"), 0) -
Application.Match(2, Range("Sheet1!$1:$1"), 0) > 1 Then

But I don't see how to form:

.Columns("B:C").Delete



- Ronald K.

Sub delcolbandcif()
If Application.Match(2, Rows(1), 1) - _
Application.Match(2, Rows(1), 0) > 1 Then _
Columns("b:c").Delete
End Sub

On Oct 18, 1:21*pm, "kittronald" <kittron...@yahoo.com> wrote:
> * * I'm trying to programmatically delete columns based on a criteria..
>
> * * If A1=1 and B1=2, do nothing.
>
> * * If A1=1, B1<>2, C1<>2 and D1=2, delete columns B:C.
>
> * * I could test with the following:
>
> * * * * If Application.Match(1, Range("Sheet1!$1:$1"), 0) -
> Application.Match(2, Range("Sheet1!$1:$1"), 0) > 1 Then
>
> * * But I don't see how to form:
>
> * * * * *.Columns("B:C").Delete
>
> - Ronald K.

Don,

Thanks for the quick response.

The problem I'm running into is populating the range in
Columns("...").Delete, which can vary in its value.

The range could be "B:B", "B:E", etc.



- Ronald K.

Hi Ronald,

Am Tue, 18 Oct 2011 15:24:05 -0400 schrieb kittronald:

> The problem I'm running into is populating the range in
> Columns("...").Delete, which can vary in its value.
>
> The range could be "B:B", "B:E", etc.

try:
LCol = Cells(1, Columns.Count).End(xlToLeft).Column
For i = LCol To 2 Step -1
If Cells(1, i) <> 2 Then
Columns(i).Delete
End If
Next


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Claus,

Is there a way to determine the range and perform the deletion in one
pass ?



- Ronald K.

On Oct 18, 2:24*pm, "kittronald" <kittron...@yahoo.com> wrote:
> Don,
>
> * * Thanks for the quick response.
>
> * * The problem I'm running into is populating the range in
> Columns("...").Delete, which can vary in its value.
>
> * * The range could be "B:B", "B:E", etc.
>
> - Ronald K.

Sub delcolbandcifSAS()
lc = Application.Match(2, Rows(1), 1)
fc = Application.Match(2, Rows(1), 0)
If lc - fc > 1 Then Columns(fc).Resize(, lc - fc).Delete
End Sub

Claus,

Vielen dank !

I'm learning a lot from you guys !


- Ronald K.

Don,

Got it !

Sub Delete_Excess_Columns()

Dim a, z As Integer

a = Application.Match(1, Rows(1), 0)
z = Application.Match(2, Rows(1), 1)

If z - a > 1 Then Columns(a + 1).Resize(, z - (a + 1)).Delete

End Sub


Is the reason you don't have to type "End If" because ELSE wasn't used ?

Is As Integer the right data type and is it really necessary to specify
it ?

Now I know how to use the Range.Resize property.

Thanks a lot !



- Ronald K.