Deleting Columns with VBA

[kittronald]

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 Guillett]

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

 

[kittronald]

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.

 

[Claus Busch]

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

 

[kittronald]

Claus,

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



- Ronald K.

 

[Don Guillett]

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

 

[kittronald]

Claus,

Vielen dank !

I'm learning a lot from you guys !


- Ronald K.

 

[kittronald]

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.