Loop through columns then delete all remaining columns

gazingdown

Board Regular
Joined
May 21, 2003
Messages
109
Should be simple but my brain isn't working these days :rolleyes:

Anyway, I need to loop through row 1 in each column in a sheet and once the value is met (e.g. when D1 or H1 or AB1 etc. > 340) then delete all columns from that column onwards. The loop can then stop of course. I'm tryint to avoid the use of 'select' to make the code run more efficiently.

So far..basically the commented lines I can't figure out.
For Each CellCheck In wsBudget.Range("C1:IV1")

If CellCheck.Value > 340 Then
'delete all columns from here onwards
'then exit the CellCheck loop only

Next CellCheck
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

This isn't the best code in the world, but it seems to work.

I just can't get my head round selecting columns properly at the moment, it's probably becasue it's my lunch break.

Code:
Sub DELETE_COLUMNS()
For MY_COLUMN = 3 To Range("IV1").End(xlToLeft).Column
If Range("A1").Offset(0, MY_COLUMN).Value > 340 Then
    Range("a1").Offset(0, MY_COLUMN).Select
    Range(ActiveCell.Offset(0, 1).Address & ":IV65536").Delete
    Exit Sub
End If
Next MY_COLUMN
End Sub

Is this suitable?
 

Haluk

Rules Violation
Joined
Oct 26, 2002
Messages
1,075
An alternative:

(Remember to backup your sheet before trying the code)

Code:
Sub Test()
Dim No1 As Integer
Dim MyRng As Range
No1 = Cells(1, 255).End(xlToLeft).Column
    For Each MyRng In Range(Cells(1, 1), Cells(1, No1))
        If MyRng >= 340 Then Range(Columns(MyRng.Column), Columns(255)).Delete
    Next
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,837
Messages
5,598,375
Members
414,233
Latest member
WolverineNurse

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top