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" 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" 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.