Deleting Columns with VBA

  • Thread starter Thread starter kittronald
  • Start date Start date
K

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.
 
Sub delcolbandcif()
If Application.Match(2, Rows(1), 1) - _
Application.Match(2, Rows(1), 0) > 1 Then _
Columns("b:c").Delete
End Sub
 
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.
Click to expand...

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
 
Claus,

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



- 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.
Click to expand...

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
 
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.
 
Back
Top