Code to delete columns meeting a condition

C

CEG

I need to compare cells I2:BE2 with cell H1 and delete the columns which are
greater than H1.

I'm sure it's simple, but I'm just learning.
 
D

Dave O

I got results with this:

Sub DeleteColumnsGreaterThanH1()
Range("be2").Select
Do While Selection.Column > 8
If ActiveCell.Value > Range("h1").Value Then
Selection.EntireColumn.Delete
ActiveCell.Offset(0, -1).Select
Loop
End Sub
 
D

Dave O

FYI
I looked at the posted version of my message, and one of the lines
wrapped and continued on the next line. If you copy and paste it as I
see it into your VBA editor, the code will give compile errors to the
effect of "if with no end if".

This line of code:
If ActiveCell.Value > Range("h1").Value Then

should be followed on the same line with
Selection.EntireColumn.Delete
 
G

Gord Dibben

You need an End If

Sub DeleteColumnsGreaterThanH1()
Range("be2").Select
Do While Selection.Column > 8
If ActiveCell.Value > Range("h1").Value Then
Selection.EntireColumn.Delete
ActiveCell.Offset(0, -1).Select
End If
Loop
End Sub


Gord Dibben MS Excel MVP
 
D

Dave O

With all due respect to all contributors: my original post did not
contain an End If because the IF structure had only one instruction.
Unfortunately the newsgroup line-wrapped, as I explained earlier;
adding the End If prevents the Offset from occurring on every loop,
which is required to consider all columns in that range.

CEG, please concatenate the two lines indicated in my responding post.
 
Top