Find criteria in a row then delete columns past but to a stop point

[Danoz]

This is a different topic, in the previous one it is to delete rows, and this one is to delete columns. I suggest you create a new thread.

Thanks Dante Amor for your assistance with the rows query.

This is a separate query regarding deleting columns once criteria is met, then stopping at a point.



I have a variable named value "MAX" which can be between 1 & 12. 1 to 12 is numbered in 'E3' to 'P3' of the worksheet, 'A3' to 'D3' are blank. I want to delete the columns where the value is greater than MAX, but not if MAX =12, or to stop once the script hits 12 in 'P3'.

Here's what I have so far, which is based on a 2009 q&a somewhere in the forum so apologies if it's outdated.

"Delete unneeded columns in selections"

Dim LR As Long, i As Long
Application.ScreenUpdating = False
If MAX = 12 Then End If -------this is where it sticks-------
Else:LR = Range(Columns.Count & "3").End(xlLeft).Column
For i = LR To 1 Step -1
With Range(i & "3")
If .Value = 12 Then End If
Else:
If .Value > MAX Then .Resize(7).EntireColumn.Delete
End With
Next i
Application.ScreenUpdating = True


I've had a go at amending since the first attempt, but still no luck. Here's my current version that doesn't work.

' Delete unneeded columns in selections
    If MAXPYMTS >= 12 Then
        Range("B1").Select
        
        End If
        
    LR = Range(Columns.Count & "3").End(xlLeft).Column
    For i = LR To 1 Step -1
        If MAXPYMTS <= "11" And .Value > MAXPYMTS Then
        With Range(i & "3")
        .Select.EntireColumn.Delete
        End With
    Next i
    
    Else:
        If .Value = 12 Then
    End If
        
    Range("B1").Select
        
    Application.ScreenUpdating = True

 

[Fluff]

How about

Sub test()
   Dim Mx As Variant
   Mx = InputBox("Max value")
   If Mx < 12 Then Columns(5 + Mx).Resize(, 12 - Mx).Delete
End Sub

 

[Danoz]

How about

Sub test()
   Dim Mx As Variant
   Mx = InputBox("Max value")
   If Mx < 12 Then Columns(5 + Mx).Resize(, 12 - Mx).Delete
End Sub

Perfect

 

[Fluff]

My pleasure

 

[DanteAmor]

Im glad to help you, thanks for the feedback.