Deleting Columns with VBA
 

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.

Deleting Columns with VBA
 

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.

Deleting Columns with VBA
 

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.

Deleting Columns with VBA
 

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

Deleting Columns with VBA
 

Claus,

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



- Ronald K.

Deleting Columns with VBA
 

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

Deleting Columns with VBA
 

Claus,

Vielen dank !

I'm learning a lot from you guys !


- Ronald K.

Deleting Columns with VBA
 

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.